Google Sheets API: Values pushed to sheet are percents

Question

I'm writing a python script to update a google sheet. I first had written it so that it updates each sheet's values individually. Obviously, it's more efficient to write so that it batch updates, but in doing that, all the values that are pushed through become percents. For example, 1 becomes 100%, 53.2 becomes 5320% and so on.

Sample code

Here is the code I'm using to apply the batch update. Treat rangelist as a list object that contains the A1 range for the cell to be written to, and valueslist to be a list object that contains the values to be written to those ranges. There are no malfunctions with rangelist.

data = []
for i in xrange(len(rangelist)):
    data.append({'range': rangelist[i], values': valueslist[i]
body = {'valueInputOption': 'USER_ENTERED', 'data': data}
response = service.spreadsheets().values().batchUpdate(spreadsheetId=ID, body = body)

Things I have tried:

Switching valueInputOption to RAW
Casting each value in valueslist as a string with '=' at the beginning

Do you mean to say this only happens when you use batchUpdate but not spreadsheets.values.update? — noogui
Yes. And only when I send the values as ints/floats. For some reason, casting them as strings fixed it. — riders994

riders994 riders994 · Accepted Answer · 2017-12-21T15:58:36

Apparently, casting the values to strings before putting them in the batch lists fixes this problem.

Google Sheets API: Values pushed to sheet are percents

1 Answers