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

CodePudding user response：

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:

• Method: spreadsheets.values.append