2

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.

arcrub
  • 170
  • 1
  • 11

2 Answers2

1

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.

alberto vielma
  • 2,302
  • 2
  • 8
  • 15
  • Thank you!! So I shouldn't be using gspread at all then. The code above works perfectly well for my data. However, in only includes the data itself, not the column headers nor row indexes. How could I include these too? Can it only be done separately? – arcrub Apr 25 '20 at 11:02
  • it is better to use the official [Google Sheets API](https://developers.google.com/resources/api-libraries/documentation/sheets/v4/python/latest/index.html) than the non-official Gspread. Check [this question](https://stackoverflow.com/questions/40554179/how-to-keep-column-names-when-converting-from-pandas-to-numpy) (the one with 7 upvotes), it will give you an idea on how to take the headers. – alberto vielma Apr 27 '20 at 07:15
1

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.

Burnash
  • 3,181
  • 2
  • 31
  • 35
  • 1
    Okay, great! That's exactly what I was looking for. In what ways is Sheets API more powerful? – arcrub Apr 27 '20 at 09:31
  • 1
    Sheets API covers an enormous feature set, almost everything the user can do via Google Sheets UI, maybe even more. Check out the massive [batchUpdate Requests reference](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request) for example. On the contrary the goal of gspread is to provide some shortcuts to get things done fast. So if you're building a complex app that heavily depends on Google Sheets API, I'd go with an official client. For a quick solution, experimenting or sketching gspread may save you some typing :) – Burnash Apr 27 '20 at 10:52