3

I am trying to copy contents of a spreadsheet into another spreadsheet but the error keeps popping up. Could you help to figure out why is it not copying over the data from one sheet to the other? This is the dummy data I am experimenting on in the original sheet:

1   CHANGED London 
2   5   Budapest
3   4   Prague
4   11  Madrid
4   11  Madrid

This is how the destination sheet looks like:

2   5   Budapest
2   5   Budapest
2   5   Budapest

This is part of the error I get:

Invalid JSON payload received. Unknown name "Weather" at 'data.values[0]': Cannot find field.
Invalid JSON payload received. Unknown name "City" at 'data.values[0]': Cannot find field.
Invalid JSON payload received. Unknown name "Number" at 'data.values[1]': Cannot find field.
Invalid JSON payload received. Unknown name "Weather" at 'data.values[1]': Cannot find field.
Invalid JSON payload received. Unknown name "City" at 'data.values[1]': Cannot find field.
Invalid JSON payload received. Unknown name "Number" at 'data.values[2]': Cannot find field.

And this is the script:

from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint
from googleapiclient import discovery
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope )
client = gspread.authorize(creds)

LT = client.open("LetsTry").sheet1
LT_data = LT.get_all_records()
NQ_all = client.open("NQ_all").sheet1
NQ_all_data = NQ_all.get_all_records()

service = discovery.build('sheets', 'v4', credentials=creds)
spreadsheetId = 'xyz'
range = 'Sheet1!A1:C11'
body = {
    'values': LT_data
}
result = service.spreadsheets().values().append(
    spreadsheetId=spreadsheetId, 
    range = range,
    valueInputOption= 'RAW',
    body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))
Tanaike
  • 181,128
  • 11
  • 97
  • 165
Zsuzsanna Szucs
  • 33
  • 1
  • 4

3 Answers3

2

I had the same problem and I recognized that it's because of encoding. I just set all data to utf8 and the error is fixed.

utf8_encode($yourValue);

Here is the utf encoding : https://www.php.net/manual/tr/function.utf8-encode.php

Enver
  • 542
  • 5
  • 7
1

How about this answer?

In your script, the values retrieved by LT_data = LT.get_all_records() are used to the request body of the method of spreadsheets.values.append in Sheets API. In this case of LT_data, the retrieved value is JSON object. But values of the request body is required to be 2 dimensional array. I think that the reason of your error message is this.

In order to avoid this error, how about the following modification?

Modified script:

When your script is modified, please modify as follows.

From: 
LT_data = LT.get_all_records()
To: 
LT_data = LT.get_all_values()
  • The value retrieved by get_all_values() is 2 dimensional array. So in this case, it can be used for body = {'values': LT_data}.

Note:

  • This answer supposes that you have already been able to get and put values for Google Spreadsheet using gspread and googleapis for python with the service account.
  • In your case, the Spreadsheets of client.open("LetsTry") and spreadsheetId = 'xyz' are required to be able to be accessed with the service account. Please be careful this.

References:

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

Tanaike
  • 181,128
  • 11
  • 97
  • 165
1

In my case, it was because of sending some "null" values. I solved by changing all the null values by empty string.

Carlos Buenosvinos Zamora
  • 751
  • 5
  • 5