Use gspread and Google sheets API to copy a sheet to multiple workbooks

Question

I have a google sheet file called "template". It has 2 worksheets. I have 100 other files called "Instance01", "Instance02", etc. which have 2 worksheets with the same name as the ones in "template".

I have created a new worksheet in "template", called "sheet3". I want to copy it to each of the 100 "Instance" documents, keeping the name "sheet3" in each Instance.

Can I achieve that with gspread? And how? If not, is there another way to do it programatically (or within reasonable time and effort)?

For example, how about using ``spreadsheets().sheets().copyTo()``? This might be useful for your situation. https://stackoverflow.com/q/46792942/7108653 — Tanaike, Jan 18 '19 at 00:37
Please [edit](http://stackoverflow.com/posts/43463046/edit) the question to be on-topic: include a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) that duplicates the problem. Questions seeking debugging help ("why isn't this code working the way I want?") must include: (1) the desired behavior, (2) a specific problem or error and (3) the shortest code necessary to reproduce it in the question itself. Please also see: [What topics can I ask about here?](http://stackoverflow.com/help/on-topic), and [How to Ask](http://stackoverflow.com/help/how-to-ask) — Mr.Rebot, Jan 21 '19 at 06:54
@Mr.Rebot, is my description non-minimal, non-complete or non-verifiable? The desired behavior is described in the question. There is no (2) error or (3) code, because that is what I am looking for. I have not yet tested Tanaike answer, but it seems promising. — pedrovgp, Jan 21 '19 at 15:56
@Tanaike, your answer did it. I will document it here as an answer, but if you wish to add your comment as an answer, I will select it as the right one to give the appropriate credits. — pedrovgp, Jan 24 '19 at 19:40
@pedrovgp At first, thank you for replying. I'm glad your issue was resolved. And also thank you for your concern. I posted an answer. Could you please confirm it? If you need more information, please tell me. I would like to modify it. — Tanaike, Jan 24 '19 at 22:46

score 1 · Answer 1 · answered Jan 24 '19 at 20:10

@Tanaike comment showed the way. I document the code I used below.

# Build the service and gspread agent
sheets_service = build('sheets', 'v4', credentials=credentials)
gc = gspread.authorize(credentials)

# Create functions to rename the sheet after copying
# For renaming sheets
# https://stackoverflow.com/questions/38074069/how-do-i-rename-a-worksheet-in-a-google-sheets-spreadsheet-using-the-api-in-py
def batch(requests, spreadsheetId, service):
    body = {
        'requests': requests
    }
    return service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId, body=body).execute()

def renameSheet(spreadsheetId, sheetId, newName, service):
    return batch({
        "updateSheetProperties": {
            "properties": {
                "sheetId": sheetId,
                "title": newName,
            },
            "fields": "title",
        }
    },
        spreadsheetId,
        service)

# Now execute the copies

# The ID of the spreadsheet containing the sheet to copy. Everybody has access!!!
spreadsheet_id = ORIGINAL_spreadsheet_id # template workbook

# The ID of the sheet to copy. Everybody has access!!!
sheet_id = original_sheet_id # template sheet id

for workbook_id in COPY_TO: #COPY_TO is a list of workbooks ids
    print(workbook_id)
    destiny_spreadsheet_id = workbook_id
    copy_sheet_to_another_spreadsheet_request_body = {
      "destinationSpreadsheetId": destiny_spreadsheet_id
    }

    request = sheets_service.spreadsheets().sheets().copyTo(spreadsheetId=spreadsheet_id, sheetId=sheet_id, body=copy_sheet_to_another_spreadsheet_request_body)
    response = request.execute()

    # Rename it
    copied_to_sheet = gc.open_by_key(destiny_spreadsheet_id) #template.worksheet(destiny)
    sheet = copied_to_sheet.worksheet('Copy of ' + NAME_OF_SHEET_TO_BE_COPIED)
    renameSheet(destiny_spreadsheet_id, sheet.id, NAME_OF_SHEET_TO_BE_COPIED, sheets_service)

score 0 · Accepted Answer · answered Jan 24 '19 at 22:46

As the method for copying the sheets, there is the method of "spreadsheets.sheets.copyTo" in Sheets API.

You can see a sample script at this thread. I thought that because the object of credentials can be used for both gspread and googleapiclient, it might help to implement this.

Reference:

spreadsheets.sheets.copyTo

Use gspread and Google sheets API to copy a sheet to multiple workbooks

2 Answers2

Reference: