CODEWITHSUNDEEP
pythonpandasgoogle-sheetsgoogle-sheets-api
SRev
SRev

Reputation: 39

load a csv file into google sheet using python

This would seem like a simple task but it's been eluding me for weeks. It's pretty simple. I have a CSV file on my local drive. I want to append the rows into an existing Google Sheet (already has rows which I need to keep) Here's the code:

import pandas as pd
import os.path
from googleapiclient import discovery
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from oauth2client.service_account import ServiceAccountCredentials
import gspread as gs
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
         "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
# Set variables
imp_dir = "C:\\Users\\SRevelle\\Google Drive"
credentials = ServiceAccountCredentials.from_json_keyfile_name('C:\\Users\\SRevelle\\Google Drive\\client_secret.json', scope)
client = gs.authorize(credentials)
service = discovery.build('sheets', 'v4', credentials=credentials)

#Go get the file to load
os.chdir(imp_dir)
csvData = 'data.csv'

#Load it into dataframe
df = pd.read_csv(csvData)

# The ID of the spreadsheet to update.
spreadsheet_id = '1_XXXXXXXXXXXdwEOkETjEI0HrdP5M5Jh4XXXXXXXXXX'

# Values will be appended after the last row of the table.
rangeval = 'Test!A1:H1'  

# How the input data should be interpreted.
value_input_option = 'USER_ENTERED'

# How the input data should be inserted.
insert_data_option = 'INSERT_ROWS' 

request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=rangeval, valueInputOption=value_input_option, insertDataOption=insert_data_option, body={'data':df})
response = request.execute()

Here's the dataframe results from the csv file:

    Type    Number  Date     Number Description1    Description2    CAD$    USD$
0   Visa    1211    2/9/2021    0   MISC PAYMENT    RBC CREDIT CARD -20.88  0
1   MC      123     2/9/2021    0   testtest        xyz card        101.11  0

I've tried many different things and gone through many different errors. Sometimes I use .to_json just to see what error that will give me.

The error that I get with this code is:

TypeError                                 Traceback (most recent call last)
Untitled-2 in <module>
     34 
     35 insert_data_option = 'INSERT_ROWS'
---> 36 request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=rangeval, valueInputOption=value_input_option, insertDataOption=insert_data_option, body={'data':df})
     37 response = request.execute()

~\Miniconda3\lib\site-packages\googleapiclient\discovery.py in method(self, **kwargs)
   1092 
   1093         headers = {}
-> 1094         headers, params, query, body = model.request(
   1095             headers, actual_path_params, actual_query_params, body_value
   1096         )

~\Miniconda3\lib\site-packages\googleapiclient\model.py in request(self, headers, path_params, query_params, body_value)
    158         if body_value is not None:
    159             headers["content-type"] = self.content_type
--> 160             body_value = self.serialize(body_value)
    161         self._log_request(headers, path_params, query, body_value)
    162         return (headers, path_params, query, body_value)

~\Miniconda3\lib\site-packages\googleapiclient\model.py in serialize(self, body_value)
    271         ):
    272             body_value = {"data": body_value}
--> 273         return json.dumps(body_value)
    274 
    275     def deserialize(self, content):

I see lots of people trying to do this but the solution is to download a some custom app, iteratively loop through the file or import it through the GUI. There MUST be an easier way!! Any help would be very appreciated.

Upvotes: 1

Views: 2082

Answers (1)

Tanaike
Tanaike

Reputation: 201378

In your situation, how about the following modification?

Modification points:

  • Unfortunately, at Sheets API, the dataframe cannot be directly used. This has already been mentioned by Tim Roberts's comment.
  • The property of data is not existing in the method of spreadsheets.values.append. When you want to append the values using spreadsheets.values.append, please use values.

When these points are reflected in your script, it becomes as follows.

Modified script:

From:

request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=rangeval, valueInputOption=value_input_option, insertDataOption=insert_data_option, body={'data':df})
response = request.execute()

To:

values = df.values.tolist()
request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=rangeval, valueInputOption=value_input_option, insertDataOption=insert_data_option, body={'values': values})
response = request.execute()

If you want to add the header, please modify it as follows.

values = [df.columns.values.tolist()]
values.extend(df.values.tolist())
request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=rangeval, valueInputOption=value_input_option, insertDataOption=insert_data_option, body={'values': values})
response = request.execute()

Reference:

Upvotes: 2

Related Questions