Search code examples
pythongoogle-sheets-apigspread

Question on using gspread to retrieve data and update

I have a google spreadsheet that I want to retrieve: This is how the data appears on google sheet. 'Business' is the first header and the second row 'Name','Age','Fav color' are the second headers.

Business
Name Age Fav color
a1 b1 Green
a2 b2 Green
a3 b3 Green 
import gspread
import pandas as pd
import gspread_dataframe as gd

worksheet = sh.get_worksheet(index[0])
df = pd.DataFrame(final_worksheet.get_all_records())

When I get the worksheet, the output shows up as:

Business
Name Fav color
a1 Green
a2 Green
a3 Green

Which isn't what I am looking for.

Question:I am hoping that I can grab the dataframe with Name, Age, Fav color as column headers (without the Business header) and then allow me to insert some values so that the final output (on google spreadsheet) would be:

Business
Name Age Fav color
John 99 Blue
a1 b1 Green
a2 b2 Green
a3 b3 Green

Here is the code that I used to update my spreadsheet:

final_updated = updated.append(final_output)
gd.set_with_dataframe(final_worksheet, final_updated)
Solution

  • Modification points:

    • When the values from your sheet are retrieved with final_worksheet.get_all_records(), the following value is returned

        [{'': 'Fav color', 'Business': 'Name'}, {'': 'Green', 'Business': 'a1'}, {'': 'Green', 'Business': 'a2'}, {'': 'Green', 'Business': 'a3'}]
      • I thought that this might be the reason of your issue.
      • In this case, how about creating the dataframe from the slice retrieved with get_all_values()?

    • About I am hoping that I can grab the dataframe with Name, Age, Fav color as column headers (without the Business header) and then allow me to insert some values so that the final output (on google spreadsheet) would be:, in this case, how about using the method of insert_rows()? I thought that in this case, the script might be simpler.

    • In your script, worksheet is declared. But it seems that final_worksheet is not declared.

    When above points are reflecvted to your script, it becomes as follows.

    Modified script 1:

    In this modified script, df = pd.DataFrame(final_worksheet.get_all_records()) is modified.

    From: 
    df = pd.DataFrame(final_worksheet.get_all_records())
    To: 
    v = final_worksheet.get_all_values()
df = pd.DataFrame(v[2:], columns=v[1])

    Modified script 2:

    In this modified script, the row you want to insert is put using the method of insert_rows().

    From: 
    worksheet = sh.get_worksheet(index[0])
df = pd.DataFrame(final_worksheet.get_all_records())
    To: 
    worksheet = sh.get_worksheet(index[0])
worksheet.insert_rows([['John', 99, 'Blue']], row=3, value_input_option='USER_ENTERED')
    • In this case, the value of [['John', 99, 'Blue']] can be directly inserted to the row 3.

    References: