Is there a way to append data to google sheets while specifying the header?

Osman Bernhard 0
Viewed 69

In google sheets, I have a timeseries data that has many columns

date mcdonalds kfc anw tacobell
2017-08-01 432 65 543 543
2017-08-02 76 53 74 32
2017-08-03 76 234 45 78

Every now and then, a new row is generated via python and is appended to google sheets.

This is my code snippet, the list variable is called new_list

new_list = pd.Series.tolist(df)

request = service.spreadsheets().values().append(spreadsheetId=gsheetid, range=sheet_name, valueInputOption="USER_ENTERED", insertDataOption="INSERT_ROWS", body={'values': new_list})
response = request.execute()

However in my python code, sometimes, the column order may not be the same as my google sheets and in some cases there may be even new columns that are not yet present in google sheets

date kfc burgerking anw
2017-08-07 52 52 32
2017-08-08 726 45 53

Is there a way to append data to google sheets while specifying the header?

google-sheets-api google-sheets python
1 Answers

I believe your goal is as follows.

  • You have a Spreadsheet as shown in the 1st table of your question.
  • You have a dataframe as shown in the 2nd table of your question.
  • You want to put append the values of dataframe to the Spreadsheet by checking the header row.
  • You want to achieve this using googleapis for python.
  • You have already been getting and putting values to the Google Spreadsheet using Sheets API.

In this case, how about the following sample script?

Sample script 1:

In this sample script, the values of dataframe are put into the Spreadsheet using the existing header row in your Spreadsheet. The header row of the Spreadsheet is retrieved by "Method: spreadsheets.values.update".

# This sample value is from your question.
df = pd.DataFrame([["2017-08-07", 52, 52, 32], ["2017-08-08", 726, 45, 53]], columns=["date", "kfc", "burgerking", "anw"])

gsheetid = "###" # Please set your Spreadsheet ID.
sheet_name = "Sheet1" # Please set sheet name.
service = build("sheets", "v4", credentials=creds) # Please use your script here.

header = service.spreadsheets().values().get(spreadsheetId=gsheetid, range=sheet_name).execute())["values"][0]
new_list = []
dic = df.to_dict("records")
for o in dic:
    temp = []
    for h in header:
        temp.append(o[h] if h in o else "")
    new_list.append(temp)
res = service.spreadsheets().values().append(spreadsheetId=gsheetid,range=sheet_name,valueInputOption="USER_ENTERED",insertDataOption="INSERT_ROWS",body={"values": new_list}).execute()
  • When this script is run to your sample Spreadsheet, the values of new_list of [['2017-08-07', '', 52, 32, ''], ['2017-08-08', '', 726, 53, '']] are appended to the Spreadsheet.

Sample script 2:

In this sample script, the values of dataframe are put into the Spreadsheet using the existing header row in your Spreadsheet. The header row of the Spreadsheet is retrieved by "Method: spreadsheets.values.get". And, the header row is updated by "Method: spreadsheets.values.update".

# This sample value is from your question.
df = pd.DataFrame([["2017-08-07", 52, 52, 32], ["2017-08-08", 726, 45, 53]], columns=["date", "kfc", "burgerking", "anw"])

gsheetid = "###" # Please set your Spreadsheet ID.
sheet_name = "Sheet1" # Please set sheet name.
service = build("sheets", "v4", credentials=creds) # Please use your script here.

header = service.spreadsheets().values().get(spreadsheetId=gsheetid, range=sheet_name).execute()["values"][0]
dic = df.to_dict("records")
add_header = [k for k in dic[0].keys() if k not in header]
header = [*header, *add_header]
new_list = []
for o in dic:
    temp = []
    for h in header:
        temp.append(o[h] if h in o else "")
    new_list.append(temp)
if add_header != []:
    service.spreadsheets().values().update(spreadsheetId=gsheetid,range=sheet_name,valueInputOption="USER_ENTERED",body={"values": [header]}).execute()
res = service.spreadsheets().values().append(spreadsheetId=gsheetid,range=sheet_name,valueInputOption="USER_ENTERED",insertDataOption="INSERT_ROWS",body={"values": new_list}).execute()
  • When this script is run to your sample Spreadsheet, the header row is updated by a new header row of ['date', 'mcdonalds', 'kfc', 'anw', 'tacobell', 'burgerking']. And, the values of new_list of [['2017-08-07', '', 52, 32, '', 52], ['2017-08-08', '', 726, 53, '', 45]] are appended to the Spreadsheet.

References:

Tanaike0
Related