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 ID → xxxxxxxxxxxxxxxxxxxxxxx
I used can be found at:
https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxx/