GSpread how to duplicate sheet

Question

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("[email protected]", perm_type='user', role='writer')

Answer 1

• 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("[email protected]", 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("[email protected]", 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("[email protected]", perm_type='user', role='writer'). Please be careful this.

References:

• duplicate_sheet
• copy(file_id, title=None, copy_permissions=False)

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("[email protected]", 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:

• del_worksheet(worksheet)