Home > Enterprise >  Python script to export Excel to Google Sheets
Python script to export Excel to Google Sheets

Time:11-11

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 script to export Excel to Google Sheets,-Already have an

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.

CodePudding user response:

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

  • Related