Google Sheets Api: Copy google spreadsheet

6
votes

Trying to copy a whole spreadsheet but I guess there is no api to do so.

Basically, I am trying to do the following:

  • Have a spreadsheet which I would like to duplicate with minor changes.
  • Create a new spreadsheet, copy all sheets from a template into the new spreadsheet one by one (Spreadsheet copy would have been much more efficient)

Creating new spreadsheets works alright however, copying sheets from spreadsheet doesn't work.

Tried 2 ways:

Angular:

$http.post("https://sheets.googleapis.com/v4/spreadsheets/"+fromSpreadsheetId+"/sheets/"+fromSheetId,
                            {headers: {
                        'Authorization': 'Bearer ' + this.oauthToken
                     }},

Gives following error:

Response to preflight request doesn't pass access control check: No 'Access-Control-Allow-Origin'

Google Sheets Api call:

gapi.client.sheets.spreadsheets.sheets.copyTo({spreadsheetId: fromSpreadsheetId , sheetId: fromSheetId},{destinationSpreadsheetId: destinationSpreadsheetId});

Request goes through without any error. However, the newly created spreadsheet doesn't have the sheet copied.

2
javascriptangularjsgoogle-sheets-api
When asking a question about a problem caused by your code, you will get much better answers if you provide code people can use to reproduce the problem. "It doesn't work" is not a problem statement. Tell us what the expected behavior should be. Tell us what the exact wording of the error message is, and which line of code is producing it. Put a brief summary of the problem in the title of your question. – georgeawg
@georgeawg Just edited the question making it more clear. – Shyamal Parikh
See How to use CORS to access Google APIs. – georgeawg

2 Answers

9
votes

You probably want to ask a separate question specifically for the CORS issue, since that a separate problem.

With regards to "copy a spreadsheet", you have two options:

1) Use the Drive API's files.copy method. The fileId in the Drive API is equivalent to the spreadsheetId in the Sheets API.

2) Don't use a "template" spreadsheet that you copy. Instead, use the Sheet API's spreadsheets.create method. You can use spreadsheets.get to retrieve your "template" JSON, and can tweak that as necessary before creating your new spreadsheet.

1
votes

I've also faced with this problem, so as it was written previously, the best and maybe the only right way is to use Drive API`s files.copy method. Here is PHP example how you can do it:

function copySpreadSheet(string $spreadsheetId, string $title, string email) {
    $serviceSheets = new Google_Service_Sheets(getGoogleClient());
    $serviceDrive = new Google_Service_Drive(getGoogleClient());

    $fileCopy = $serviceDrive->files->copy($spreadsheetId, new Google_Service_Drive_DriveFile());
    insertPermission($serviceDrive, $fileCopy->getId(), $email, 'user', 'owner');
    $requests = [
        'updateSpreadsheetProperties' => [
            'properties' => [
                'title'         => $title,
            ],
            'fields' => 'title'
        ]
    ];
    $requestBody = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
        'requests' => $requests,
    ]);
    $response = $serviceSheets->spreadsheets->batchUpdate($fileCopy->getId(), $requestBody);

    return $serviceSheets->spreadsheets->get($fileCopy->getId());
}

If it necessary, here is insertPermission() code:

function insertPermission(Google_Service_Drive $service, string $fileId, string $email, string $type, string $role) {
    $newPermission = new Google_Service_Drive_Permission([
        'type' => $type,
        'role' => $role,
        'emailAddress' => $email,
    ]);
    return $service->permissions->create($fileId, $newPermission, [
            'fields' => 'id',
            'transferOwnership' => 'true',
        ]
    );
}