Copy-Paste data in a Connected Data Sheet using Python
We have a client that rolls data for forecasting month-over-month. We are building a workiva forecasting tool so they can mange their data within Workiva (rather than BPC). The Actual data is stored in a separate file from the forecast data since it is imported via automation from an outside source. The Fcst data will be stored and maintained within Workiva. The worksheet has TIME in the columns. The scenario is copy FCST3 to FCST4, lock FCST3. Actual is part of a separate chain. The client can update FCST4 but they cannot touch prior forecasts.
In this PY code I am copying FCST3 to FCST4 in EXCEL.I need assistance in how to make this work in Workiva. I do understand the worksheets will be required to have SPREADSHEET and SHEEET_IDs. I am also aware we need the requirements.txt and added to this is openpyxl=3.1.1 so we can access the library. What i don't understand is how to incorporate the required elements (including ApiAuth and SSApi), and if JSON is required to communicate with the Sheet(s). I only have one example program to look at - the one that posts Hello World in cell A1. I struggled to retrain the program to perform the steps in my code below. Overall i need guidance how to use PY code that are proven successful outside Workiva.
# importing openpyxl module
import openpyxl as xl;
# opening the source excel file
filename ="C:\\Users\\c5admin\\PYTHON\\Units.Forecast.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
# use same file - append data to bottom if existing
filename1 = filename
wb2 = wb1
ws2=ws1
# get number of rows and set next row for consecutive forecast
mr = ws1.max_row
mc = ws1.max_column
#print(mr,mc)
nr = mr+1
# copying the cell values
for i in range (1, mr + 1):
# print(ws1.cell(i,1).value)
if ws1.cell(i,1).value == 'FCST3':
for j in range (1, mc + 1):
# reading cell value from source
c = ws1.cell(row = i, column = j)
#if ws1.cell(i,1).value == 'FCST3':
# writing to destination
ws2.cell(row = nr, column = j).value = c.value
ws2.cell(nr,1).value="FCST4"
if j == 22:
nr+=1
# saving the destination excel file
wb2.save(str(filename1))
print("Done")
-
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
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
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
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请先登录再写评论。
评论
4 条评论