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("/"+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.

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.

• javascript
• angularjs
• google-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 Commented Dec 20, 2016 at 18:45
• @georgeawg Just edited the question making it more clear. – Shyamal Parikh Commented Dec 20, 2016 at 19:03
• See How to use CORS to access Google APIs. – georgeawg Commented Dec 20, 2016 at 19:28

4 Answers 4

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.

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',
        ]
    );
}

This question is old, but speaking about the newest Sheets API v4, you need the requestBody field in order to copy a sheet.

sheets.spreadsheets.sheets.copyTo({
    spreadsheetId: fromSpreadsheetId,
    sheetId: fromSheetId,
    requestBody: {
        destinationSpreadsheetId: destinationSpreadsheetId
    }
})

Check the docs, but notice that in their example they use resource instead of requestBody. I'm using TypeScript and I got a type error using resource.

Here's a TypeScript example changing docs' example where I duplicate a sheet in the same spreadsheet (for duplicate, one can also go for the duplicate request using batchUpdate).

const request: sheets_v4.Params$Resource$Spreadsheets$Sheets$Copyto = {
  // The ID of the spreadsheet containing the sheet to copy.
  spreadsheetId: '9MhcPzrWE-Pv9MKhJwow6cWi9uO46RcDTfxhvT9X1fY',
  // The ID of the sheet to copy.
  sheetId: 3,
  requestBody: {
    // The ID of the spreadsheet to copy the sheet to.
    destinationSpreadsheetId: '9MhcPzrWE-Pv9MKhJwow6cWi9uO46RcDTfxhvT9X1fY'
  }
};

try {
  const response = (await this.sheets.spreadsheets.sheets.copyTo(request)).data;
  console.log(JSON.stringify(response, null, 2));
} catch (err) {
  console.error(err);
}

I used a combination of the top two answers above:

effectively, I did the following :

copied spreadsheet with this function `

function copySpreadSheet(string $spreadsheetId, string $title, string $email) {
        $serviceSheets = new Google_Service_Sheets($this->getClient());
        $serviceDrive = new Google_Service_Drive($this->getClient());

        $fileCopy = $serviceDrive->files->copy($spreadsheetId, new Google_Service_Drive_DriveFile());

        $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());
    }

and then used this snippet to change ownership to me

$drive = new Google_Service_Drive($client);

$newPermission = new Google_Service_Drive_Permission();

$newPermission->setEmailAddress('[email protected]');

$newPermission->setType('group');

$newPermission->setRole('writer');

$response = $drive->permissions->create($newSpreadsheetId, $newPermission);