CODEWITHSUNDEEP
javascriptangularjsgoogle-sheets-api
Shyamal Parikh
Shyamal Parikh

Reputation: 3068

Google Sheets Api: Copy google spreadsheet

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

Basically, I am trying to do the following:

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.

Upvotes: 9

Views: 15268

Answers (4)

Kevin Marion
Kevin Marion

Reputation: 1

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);

Upvotes: 0

Francisco Gomes
Francisco Gomes

Reputation: 1867

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

Upvotes: 1

INNO
INNO

Reputation: 36

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

Upvotes: 1

Sam Berlin
Sam Berlin

Reputation: 3773

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.

Upvotes: 10

Related Questions