クラシックファイルタイプは、2021年1月をもって使用不可となりました。クラシックファイルを移行するか、PDFをダウンロードすることができます。 詳細を見る

Copy-Paste data in a Connected Data Sheet using Python

0

コメント

4件のコメント

  • Deb Silverman

    Rather than putting out a blank slate - here is my reasonable effort to get the posted python code into Workiva  - in the Main() rows 53- 55 to get url and two print statements - i need to post the DoCopy data after the last row of the existing worksheet. In this test code I am making a copy of FCST3 to FCST4 into DoCopy[].  I need to post DoCopy, which is the new data.  I will need to make the FCST# dynamic - one step at a time - i just need to get this working. 

    import requests
    import json
    import os

    AUTH_URL = "https://api.app.wdesk.com/iam/v1/oauth2/token"
    SS_API_URL = 'https://api.app.wdesk.com/platform/v1/spreadsheets/'
    CLIENT_ID = '3986....'
    CLIENT_SECRET = '227c0812....'
    SPREADSHEET_ID = 'efb2aa8c323a47f49f2183205c0d80aa'
    SHEET_ID = 'd89db1abad6d40c596bafe7c124fb323'

    DoCopy = [] 
    class ApiAuth:
       def __init__(self):
           self._headers = {'Content-Type': \
           'application/x-www-form-urlencoded;charset=UTF-8'}

       def getAuthToken(self):
           tokenRes = requests.post(AUTH_URL, data = 'client_id=' + CLIENT_ID + \
           '&client_secret=' + CLIENT_SECRET + '&grant_type=client_credentials', \
           headers = self._headers)
           tokenResponse = json.loads(tokenRes.text)
           return tokenResponse['access_token']

    class SSApi:
       def __init__(self, accessToken):
           self._accessToken = 'Bearer ' + accessToken
           self._ssApiUrl = SS_API_URL + SPREADSHEET_ID + "/sheets/" + SHEET_ID + "/values/A2:U"
           self._headers = {'Authorization': self._accessToken}

        def getRawData(self):
           dataRes = requests.get(self._ssApiUrl, headers = self._headers)       
           print(dataRes.text)        
           rawData = json.loads(dataRes.text)
           last_item = rawData['data'][0]['values'][-1]
           print("last Item ", last_item)
           first_item = rawData['data'][0]['values'][1]
           print("first Item ", first_item)
           return rawData['data'][0]['values']

    def main():
       authToken = ApiAuth().getAuthToken()
       ssApi = SSApi(authToken)
       rawData = ssApi.getRawData()

        for i in rawData:       
           if i[0]=='FCST3':            
               DoCopy.append(i) 
               i[0]='FCST4'  
               #print(i)

        url = SS_API_URL + SPREADSHEET_ID + "/sheets/" + SHEET_ID + "/values/" #+ range
       print("Update Range: ", url)
       print("Values: ", DoCopy) 

    print('Calling main')
    main()

     

    0
  • Liz Walters

    Hi Deb,

    Thank you for your question! It looks like this existing python script can interact with an .xlsx file, but needs to be converted into Workiva platform public API calls. Our public API documentation, which includes how to access various properties about spreadsheets as well as authenticating with our APIs, may be helpful here. In particular, this API to copy sheets may be what you're looking for. If you have any questions after utilizing these resources, we can connect further with our engineers.

    Happy Friday!

    0
  • Deb Silverman

    We are  not interacting with an external excel file. The first block of code used excel because it was familiar and serves as proof of logic.

    We need to update the data sheets, specific to Workiva scripting.

    • We are not copying sheets.  We are appending data to an existing Data Sheet. 
    • Getting the last row. I can get last row values via [-1]. How do I get the row number to append sheets?
    • In Workiva scripting, how do we define the range of rows no starting at A1?

    url = SS_API_URL + SPREADSHEET_ID + "/sheets/" + SHEET_ID + "/values/" #+ range

    The resources on workiva’s site:

    The code example you provided to copy sheet  and it copies the sheets contents at the top level of its destination with  index = 0.  the issue here is my range isn't going to start with 0, rather it is determined at run time.

    import requests
    headers = {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer {access-token}'
    }
    r = requests.post('https://api.app.wdesk.com/platform/v1/spreadsheets/{spreadsheetId}/sheets/{sheetId}/copy', headers = headers)
    print(r.json())
    Spreadsheets properties – we are not exporting, changing sheet index, name, parent, or any of the properties listed.  I need to grab the range of the dataset so I can append to it.

    Dataset

    Properties

    Name

    Type

    Description

    Restrictions

    range

    string

    A1 style notation describing the range. Datasets are always located in the top left-hand corner of the sheet, so there is no need to specify range when creating a dataset.

    read-only

    sheet

    string

    The unique identifier of the sheet to which this dataset belongs.

     

    values

    [array]

    A row-major ordered multidimensional array of cell values.

     

     

    In your example we have to set the range – how do you do this dynamically??

    {
    "range": "A1:B2",
    "sheet": "27f1b61c04ae4b0991bc73c631914e1d",
    "values": [
       [
         1,
         4
       ],
       [
         2,
         ""
       ]
    ]
    }

     

    How do we set these dynamically? In my code I know the last row of the dataset using [-1] but this isn’t useful for appending.  

    {
    "startColumn": 0,
    "startRow": 0,
    "stopColumn": 0,
    "stopRow": 0
    }

     In the SheetUpdate properties, I don’t see anything to lock the cell ranges.

     I appreciate assistance with these questions, Thank you

    0
  • Jeff Hickey

    Hello Deb,

    I think you could achieve this with two potential API endpoints. First, you could use the Retrieve a list of range endpoint to get all data from the Sheet. This endpoint will return a @nextLink URL to allow you to step through the data until you've retrieved what is needed. You can specify the range parameter if you don't want it to start at row 0. For example "B1:" This endpoint will return an array of "data". It will also include the "range" retrieved which you can use to determine your next row. For example, if your Sheet had data from A1 to E25, and you specific the range in your call as "B1:", then the range value returned would be "range": "B1:E25". The URL would look something like this "https://api.app.wdesk.com/platform/v1/spreadsheets/<spreadsheet_ID>/sheets/<sheet_ID>/values/B1:". With this, you would know that your next row is 26.

    "data":[{"range": "B1:E25"}]

    The endpoint also returns a list of "values". These values could be iterated over to also determine the last row and to look for "FCST3" values that you plan to move into row 26+ for FCST4. The values can be used to determine the total number of columns as well. For example "values": [[ "First", "Second", "Third" ]] would mean that column 4 (or D) is the next column.

    Retrieve data from a sheet endpoint may be helpful as well. This endpoint includes additional information besides just the values, such as formatting. Similar to the above example, you can specify the range with the query parameter $cellrange. This endpoint will return a nextLink URL to allow you to step through the data until you've retrieved what is needed. It also include "range" which is 0 indexed and can be used to figure out the last row and column in the Sheet. In the below example, column 27 and row 3 are next columns and rows.

    "range": {
               "startColumn": 0,
               "startRow": 0,
               "stopColumn": 25,
               "stopRow": 1
            }

    After retrieving the data using the method above that works best for you, the data could be iterated over to generate the new data to write back to the Sheet. Once the new values are built, they can be sent via the Update values in a range endpoint to write back to the Sheet.

    Keep in mind that Spreadsheet do have limits. You can quickly run into these limits if you're programmatically writing to Spreadsheets. One limit to keep in mind is that a Spreadsheet is limited to 2 million cells of data. If the account has access to Wdata, you may want to consider using Wdata Tables to store this data rather than Spreadsheets. Tables have much larger capacity and are designed to store datasets that can be reported on by querying the data and even sending the query output to a Spreadsheet. Additionally, Chains and Pipelines could be used to do much of the heavy lifting in processing the data.

    0

サインインしてコメントを残してください。