4

After googling and searching on Stackoveflow, I think I can't find a guide on how to duplicate existing sheet(existing Template sheet) and saving it into another sheet.

as per docs, there is duplicate_sheet but I can't manage to do a working example, anyone that can guide me with this?

 import gspread
 from gspread.models import Cell, Spreadsheet

 scope = [
 "https://www.googleapis.com/auth/spreadsheets.readonly",
 "https://www.googleapis.com/auth/spreadsheets",
 "https://www.googleapis.com/auth/drive.readonly",
 "https://www.googleapis.com/auth/drive.file",
 "https://www.googleapis.com/auth/drive",
 ]

 json_key_absolute_path = "key.json"
 credentials = ServiceAccountCredentials.from_json_keyfile_name(json_key_absolute_path, scope)
 client = gspread.authorize(credentials)

 spreadsheet_client = Spreadsheet(client)
 spreadsheet_client.duplicate_sheet("18Qk5bzuA7JOBD8CTgwvKYRiMl_35it5AwcFG2Bi5npo", new_sheet_name="timcard2")
 worksheet = client.open("timcard2")
 worksheet.share("my_email@google.com", perm_type='user', role='writer')
Tanaike
  • 181,128
  • 11
  • 97
  • 165
Led
  • 662
  • 1
  • 19
  • 41

1 Answers1

8
  • You want to copy the source Spreadsheet as new Spreadsheet.
  • You want to achieve this using gspread with python.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

If my understanding is correct, how about this answer?

Issue and solution:

It seems that duplicate_sheet method of gspread is used for copying a sheet in the source Spreadsheet to the same source Spreadsheet. Ref In order to copy the source Spreadsheet as new Spreadsheet, pleas use the method of copy() of Class Client.

Sample script:

Please modify your script as follows.

From: 
client = gspread.authorize(credentials)

spreadsheet_client = Spreadsheet(client)
spreadsheet_client.duplicate_sheet("18Qk5bzuA7JOBD8CTgwvKYRiMl_35it5AwcFG2Bi5npo", new_sheet_name="timcard2")
worksheet = client.open("timcard2")
worksheet.share("my_email@google.com", perm_type='user', role='writer')
To: 
client = gspread.authorize(credentials)
client.copy("18Qk5bzuA7JOBD8CTgwvKYRiMl_35it5AwcFG2Bi5npo", title="timcard2", copy_permissions=True)

worksheet = client.open("timcard2")
worksheet.share("my_email@google.com", perm_type='user', role='writer')
  • When you run the script, the Spreadsheet which has the spreadsheet ID of 18Qk5bzuA7JOBD8CTgwvKYRiMl_35it5AwcFG2Bi5npo is copied as the spreadsheet name of timcard2. And, the permission information of the source Spreadsheet is also copied.

Note:

  • In this case, when copy_permissions=True is used, the permission information is also copied. So although I'm not sure about your actual situation, it might not be required to use worksheet.share("my_email@google.com", perm_type='user', role='writer'). Please be careful this.

References:

Added:

  • You want to copy one of sheets in Google Spreadsheet.

I could understand like above. For this, the sample script is as follows.

Sample script:

client = gspread.authorize(credentials)
client.copy("18Qk5bzuA7JOBD8CTgwvKYRiMl_35it5AwcFG2Bi5npo", title="timcard2", copy_permissions=True)

ss = client.open("timcard2")
ss.share("my_email@google.com", perm_type='user', role='writer')

delete_sheets = ["Sheet2", "Sheet3", "Sheet4"]  # Please set the sheet names you want to delete.
for s in delete_sheets:
    ss.del_worksheet(ss.worksheet(s))
  • In this sample, the sheets of "Sheet2", "Sheet3", "Sheet4" are deleted from the copied Spreadsheet.

Reference:

halfer
  • 19,824
  • 17
  • 99
  • 186
Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks, I will check it as soon as I update my project – Led Apr 09 '20 at 08:15
  • Hi, thanks its working but ... can it copy a specific sheet only? its copying the whole spreadsheet. Or I just open the spreadsheet and delete the unwanted sheets? – Led Apr 21 '20 at 05:20
  • 1
    @Led Thank you for replying. I couldn't notice that you want to copy one of sheets in Spreadsheet. This is due to my poor English skill. I deeply apologize for this. In your situation, I would like to recommend to copy the Spreadsheet and delete the unused sheets in the copied Spreadsheet. Because when `client.copy()` is used, the permission information can be also copied. I thought that this will be useful for your situation. For this, I added one more script. Could you please confirm it? – Tanaike Apr 21 '20 at 05:46
  • i see, thanks for confirming. I already have the logic in deleting the sheet, but I was thinking if that was necessary or if there was a shortcut in the .copy. Thanks for the effort, Arigatou gozaimasu? – Led Apr 21 '20 at 23:01
  • @Led Thank you for replying. I'm glad your issue was resolved. Kotirakoso Arigatou. – Tanaike Apr 21 '20 at 23:17