Home > OS >  How to upload a CSV file to a new tab(sheet) in the google spreadsheet
How to upload a CSV file to a new tab(sheet) in the google spreadsheet

Time:07-09

I am a python beginner and I want to write a script that can automatically upload the CSV file to a google spreadsheet. And following is my code to realize it:

import csv

import gspread
from oauth2client.service_account import ServiceAccountCredentials

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"]

credentials = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)

client = gspread.authorize(credentials)

sheetName = "abc" 
spreadsheet = client.open("abc")
worksheet = spreadsheet.worksheet(sheetName)
content = list(csv.reader(open('123.csv')))
worksheet.append_rows(content, value_input_option="USER_ENTERED")

However, this script can only append the values under the same sheet, and my boss asked me to upload CSV to a new tab under the same spreadsheet every day. For example, today I am going to upload CSV data to CSV-to-Google-Sheet, tomorrow I am going to upload it to sheet1 (Please check the screenshot I attached, I am sorry that my English is bad that I have to use the image to make my question clear) I googled the related information for hours but find nothing useful. What should I do?

[As you can see there are two tabs at the bottom of the sheet, one is called CSV-to-Google-Sheet, the other is called sheet1 ][1] [1]: https://i.stack.imgur.com/VWIbH.png

CodePudding user response:

As you need to put the data each day on a new page, my suggestion would be for you to create a new page with the name of today's date and then add the values:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import datetime

scope = ['https://spreadsheets.google.com/feeds']
dicter = 'client_secret.json'
creds = ServiceAccountCredentials.from_json_keyfile_name(dicter, scope)
client = gspread.authorize(creds)

id_file = 'xxxxxxxxxxxxxxxxxxxxxxx'
spreadsheet = client.open_by_key(id_file)

today = datetime.date.today()
worksheet = spreadsheet.add_worksheet(title=str(today), rows=1000, cols=26)
worksheet.update('A1',[['test','test_2']])

Additional note: Always try to use the file ID to open instead of using the name, it's a bit risky if you or someone else edits the name (it's easier for someone to edit the file name than you delete the file and create another one).

The IDxxxxxxxxxxxxxxxxxxxxxxx I used can be found at:

https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxx/
  • Related