0

I'm learning Python currently and I'm trying to create automation code for Google Sheets API and Python for a client. So far with my automation code I have successfully downloaded a csv file from a website, located the file, and pushed the file to Google sheets. I used my logic from the answer from Ufos from this question. The problem is that I repeat this process about 10 times the csv data on the Google Sheet gets overridden by the new data. My client wants to see data for all the clients in one place. I have tried to do append but can't get it to work with the retrieved csv information. I've done my best by using Google but I can't seem to get it to work. Any help would be much appreciated!

 def insertIntoSheets(self, email, owner):
    creds = service_account.Credentials.from_service_account_file(
    self.SERVICE_ACCOUNT_FILE, scopes=self.SCOPES)

    service = build('sheets', 'v4', credentials=creds)
    
    # Insert CSV information into Google Sheet
    sheet = service.spreadsheets()
                        

    csv_path = ""
    csv_files = os.listdir(self.DOWNLOADS_PATH)
    for csv_file in csv_files:
        if csv_file.startswith("lead_export"):
            print("CSV path found")
            csv_path = os.path.join(self.DOWNLOADS_PATH, csv_file)
            print(f'CSV_PATH: {csv_path}')

    if csv_path == "":
        print("No CSV file detected. Must not have downloaded. Please try again.")
    else:
        #add email and owner
        values = (
            (owner, email),
        )
        value_range_body = {
            'majorDimension': 'ROWS',
            'values': values
        }
        service.spreadsheets().values().append(spreadsheetId=self.DATA_SPREADSHEET_ID, 
            valueInputOption='USER_ENTERED',
            range="Sheet1!A1",
            body=value_range_body).execute()

        #add CSV to Google Sheets
        print(f'Attempting to upload CSV file for {email}')
        with open(csv_path, 'r') as csv_file:
            csvContents = csv_file.read()
        body = {
            'requests': [
                   
                {
                'pasteData': {
                    "coordinate": {
                        "sheetId": [sheetId],
                        "rowIndex": "0",  # adapt this if you need different positioning
                        "columnIndex": "0", # adapt this if you need different positioning
                    },
                    "data": csvContents,
                    "type": 'PASTE_NORMAL',
                    "delimiter": ',',
                }
            }
            ]
        }
        request = sheet.batchUpdate(spreadsheetId=self.DATA_SPREADSHEET_ID, body=body)
        response = request.execute()

        print(f'Deleting CSV file for {email} account')
        deleted = self.removeCSV()
        if deleted == 0:
            print(f'No CSV files detected for {email}')
        else:
            print(f'{deleted} CSV files deleted for {email}.')

        return response
Tanaike
  • 181,128
  • 11
  • 97
  • 165
RachGinger
  • 13
  • 3
  • Can you update your code such that it meets the guidance on creating a [mre]? – esqew Dec 01 '21 at 00:30
  • I have to apologize for my poor English skill. About `The problem is that I repeat this process about 10 times the csv data on the Google Sheet gets overridden by the new data.`, in this case, when you repeat your process less than 10 times, the sheet is not overwritten by new data. Is my understanding correct? – Tanaike Dec 01 '21 at 01:35
  • @Tanaike No worries! No, this function is called in a loop and the data is overwritten every time. In the request the rowIndex and columnIndex stay the same so the data is always thrown at the beginning of the Google Sheet. I need to somehow append the data. I have seen a lot of examples of appending to Google Sheets but they don't use csv files. They usually have data they are manually putting in for the rows and columns. – RachGinger Dec 01 '21 at 03:55
  • Thank you for replying. From your replying, I proposed a modification point as an answer. Could you please confirm it? If I misunderstood your question and that was not useful for your situation, I apologize. – Tanaike Dec 01 '21 at 08:27

1 Answers1

1

I believe your goal is as follows.

  • You want to append the CSV data from the file to Google Spreadsheet.
  • You wan to achieve this using googleapis for python.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

In this case, how about the following modification?

Modified script:

Please modify your script as follows.

From:

with open(csv_path, 'r') as csv_file:
    csvContents = csv_file.read()
body = {
    'requests': [
           
        {
        'pasteData': {
            "coordinate": {
                "sheetId": [sheetId],
                "rowIndex": "0",  # adapt this if you need different positioning
                "columnIndex": "0", # adapt this if you need different positioning
            },
            "data": csvContents,
            "type": 'PASTE_NORMAL',
            "delimiter": ',',
        }
    }
    ]
}
request = sheet.batchUpdate(spreadsheetId=self.DATA_SPREADSHEET_ID, body=body)
response = request.execute()

To:

with open(csv_path, newline='') as f:
    reader = csv.reader(f)
    values = list(reader)
response = service.spreadsheets().values().append(spreadsheetId=spreadsheetId, valueInputOption='USER_ENTERED', range="Sheet1", body={"values": values}).execute()
  • In this modification, import csv is used.
  • Please modify the sheet name of "Sheet1" for your actual situation.
  • In this modification, the CSV data is retrieved as a list from the file, and the list is put to the Spreadsheet as the appending values using spreadsheets.values.append method.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165