CODEWITHSUNDEEP
pythonjsonpandasgoogle-sheetsgspread
arcrub
arcrub

Reputation: 170

python (gspread) - whole data table placed in one cell of my Google Sheets instead of separate cells

My goal is to update a Google Sheets document by replacing the content of its first sheet by a table of my own data (that is, a table of instances against attributes of a class). I have attempted to use the module gspread to do so but it hasn't been working out so well: If I run the line

client.open("GoogleSheetName").sheet1.update('A1:R181',Member.display().to_json())

(where Member is my class, and display() is my own class method which simply converts my data to a pandas dataFrame object.), the whole Member.display().to_json() string is put in the cell A1, as though it is never unpacked and simply left as one string. If I use the json module and dump my data using json.dumps(Member.display().to_dict()), I obtain the same problem.

I also attempted to simply change the data to Member.display().to_dict(), in which case I get an 'Invalid JSON payload received' error.

It seems like the data may need to be in matrix format for it to work i.e. [[..,..,..][..,..,..]...], as that is the only format that has worked properly in my case. I know it works because when I test the API by running 

client.open("GoogleSheetName").sheet1.update('A1:B2', [[1, 2], [3, 4]])

each number is put in their own separate cell as wanted. Is that the format I must use?

How may I resolve that issue? It is like my data needs to be sent JSON readable but cannot be a string otherwise it is interpreted as a single argument!

Any help would be very much appreciated.

Upvotes: 2

Views: 998

Answers (2)

Burnash
Burnash

Reputation: 3321

If Member.display() is a pandas DataFrame, you can do the following to update the sheet using gspread:

df = Member.display()
ws = client.open("GoogleSheetName").sheet1
ws.update([df.columns.values.tolist()] + df.values.tolist())

Other examples on using gspread with pandas are in the docs.

Sidenote:

Of course, you can use official API as Alberto pointed out. It all depends on your use case. Google Sheets API is a lower-level API. Very powerful but requires a bit more boilerplate code. You can see this if you compare both code examples. It's a tradeoff.

Upvotes: 1

alberto vielma
alberto vielma

Reputation: 2342

As the official Sheets API says at Method: spreadsheets.values.append Request body

The request body contains an instance of ValueRange.

ValueRange is a JSON that must contain a key named values that must be an array of arrays. I made the following example based on the Python Quickstart to show you how to pass the data you want to your Spreadsheet.

  service = discovery.build('sheets', 'v4', credentials=creds)
    spreadsheet_id = 'YOUR-SHEETS-ID'
    ranges = "A1:A"
    value_render_option = "DIMENSION_UNSPECIFIED"
    value_input_option = "USER_ENTERED"

    df = pd.DataFrame({
        'age':    [ 3,  29],
        'height': [94, 170],
        'weight': [31, 115]
    })

    value_range_body = {
        "values": df.to_numpy().tolist(),
        "majorDimension": "DIMENSION_UNSPECIFIED"
    }

    request = service.spreadsheets().values()\
        .append(spreadsheetId=spreadsheet_id, range=ranges, valueInputOption=value_input_option, body=value_range_body)

    response = request.execute()
    print(response)

As you can see, I am using df.to_numpy().tolist() to convert the pandas dataframe to a NumPy array and then to a normal Python list.

Upvotes: 1

Related Questions