Python script to export Excel to Google Sheets

Question

I want to export the local excel data to google sheets. I got all APIs and requirements. Now, I'm trying to that python script in this link https://blog.coupler.io/python-to-google-sheets/#:~:text=append()-,Python%20script%20to%20export%20Excel%20to%20Google%20Sheets,-Already%20have%20an

def export_excel_to_sheets():
    spreadsheet_id = '1JCEHwIa4ZzwAiKGmAnWGfbjeVCH_tWZF6MkIU0zICwM'
    excel_data_df = pandas.read_excel('sample-excel.xlsx', sheet_name='Sheet1', skiprows = range(1, 62), engine='openpyxl')
    excel_data_df['Date'] = excel_data_df['Date'].astype(str)
    excel_data_df['Unnamed: 0'] = ''
    body = {
        'values': excel_data_df.values.tolist()
    }
    
  
    spreadsheet_service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id, body=body, valueInputOption='USER_ENTERED', range='Sheet1').execute()
    print('{0} cells appended.'.format(result \
                                       .get('updates') \
                                       .get('updatedCells')))
export_excel_to_sheets()

I have 70 rows of data and I want to export them to Google Sheets. I don't want to "skip" any rows with "skiprows = range(1,62)" so I delete or decrease the value. When I delete this code, the script gets a HTTP400 error.

excel_data_df = pandas.read_excel('example.xlsx', sheet_name='Sheet1', skiprows = range(1, 62), engine='openpyxl')

When I delete "skiprows" code, get a HttpError 400.

googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1VIWNRrBF3E9KQo_nT7ZXvT4qQYWymITAFLx2rDEGSPw/values/Sheet1:append?valueInputOption=USER_ENTERED&alt=json returned "Invalid JSON payload received. Unexpected token.
api/v1/users/home", NaN, "{ 'Content-Typ
                    ^">

I want to export all data to Google Sheets without a problem. What is your suggestion to do that? Thank you.

In order to correctly understand your current issue of Invalid JSON payload received. Unexpected token., can you provide the sample XLSX file? Because, when I tested your showing script, even when skiprows = range(1, 62) is removed using a sample XLSX file, no error occurs. I apologize for this. — Tanaike
– Tanaike, Commented Nov 11, 2022 at 7:18
I had an empty cell in my excel. I fixed it and now, it works. Thank you for paying attention. — broyt
– broyt, Commented Nov 11, 2022 at 8:05

Furkan Edizkan · Accepted Answer · 2022-11-11 07:42:41Z

0

I think answer below will solve your problem, please give it a try. If it doesn't work let me know. https://github.com/burnash/gspread/issues/680#issuecomment-561936295

edited Nov 11, 2022 at 7:42

answered Nov 11, 2022 at 6:47

Furkan Edizkan

591 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Solar Mike Over a year ago

These links should be comments.

Furkan Edizkan Over a year ago

I can't comment, I have 48 reputation

Solar Mike Over a year ago

Expand your answer so it is not link only then I will give you a vote…

broyt Over a year ago

The problem is I have an empty cell in my excel. I add "None" value. This works for me. Thank you.

Collectives™ on Stack Overflow

Python script to export Excel to Google Sheets

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related