Append rows to Google Sheets using gspread or googleapiclient

Question

Task: I have a dataframe and I would like to append that data to a Google Sheet. The sheet has exactly the same columns as the DF and has exisiting data which I don't want to overwrite, but just add rows at the end of the sheet using the values from the DF.

Tried so far:

1. I've followed the documentation on gspread using this code:

    import json
    df2.to_json(orient = 'columns')
    values = df2.to_json(orient = 'columns')
    wks.append_row (values, value_input_option='USER_ENTERED')

Result: APIError: { "error": { "code": 400, "message": "Invalid value at 'data.values[0]' (type.googleapis.com/google.protobuf.ListValue),

2. I've followed the documentation on Google's API reference: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append

!pip install --upgrade google-api-python-client

from pprint import pprint
from googleapiclient import discovery
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive',
        'https://www.googleapis.com/auth/drive.file',
        'https://www.googleapis.com/auth/spreadsheets']

credentials = ServiceAccountCredentials.from_json_keyfile_name('NAME.json', scope)

gc = gspread.authorize(credentials)

wks = gc.open("SHEETNAME").sheet1

service = discovery.build(wks,'v1', credentials)

spreadsheet_id = 'MYID'

value_input_option = INSERT_ROWS 

values = df2.to_json(orient = 'columns')

insert_data_option = values 

request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, insertDataOption=insert_data_option, body=value_range_body)
response = request.execute()

pprint(response)

Result: AttributeError: 'ServiceAccountCredentials' object has no attribute 'request'

Question: Is either of these solutions the right approach and how can I fix the errors? Or is there a totally different option that is much easier?

Thank you!

Coda

Could you share the code you're using in (2)? Do you want to append the rows with a Service Account? – Militant

Yes, I've setup a Service Account. Code now shared in question. Thanks! – Coda

Answer 1

• You want to append the values of df2 to Google Spreadsheet.
• You want to achieve this using gspread with the Service account.
• You have already been able to get and put values for Google Spreadsheet using Sheets API with the Service account.

If my understanding is correct, how about this answer? Please think of this as just on of several possible answers.

Modification points:

• In your case, how about using df2.values.tolist() instead of df2.to_json(orient = 'columns')? By this, the values are converted to the 2 dimensional array.
• When there are several rows in the values of df2, I recommend to use the method of values_append instead of append_row. Because append_row is used for the 1 dimensional array. In this case, when the values of several rows are used, append_row is required to be used in the loop. When values_append is used for the values of several rows, the values can be put by one API call.

Modified script:

When your script is modified, please modify as follows.

spreadsheetId = '###'  # Please set the Spreadsheet ID.
sheetName = 'Sheet1'  # Please set the sheet name.

client = gspread.authorize(credentials)
sh = client.open_by_key(spreadsheetId)
df2 = pd.DataFrame({'col1': ['a1', 'a2', 'a3'], 'col2': ['b1', 'b2', 'b3']})  # This is a sample value.
values = df2.values.tolist()
sh.values_append(sheetName, {'valueInputOption': 'USER_ENTERED'}, {'values': values})

• df2 = pd.DataFrame({'col1': ['a1', 'a2', 'a3'], 'col2': ['b1', 'b2', 'b3']}) is a sample value. When this is used, the values are appended to the sheet.

References:

• values_append(range, params, body)
• Method: spreadsheets.values.append

If I misunderstood your question and this was not the direction you want, I apologize.

Grandmamma