Duplicate Google Sheets Sheet Using Google Sheets API PHP Client

Using the method spreadsheets.batchUpdate with request DuplicateSheetRequest you can quickly duplicate a Google Sheets sheet. The source sheet ID is required, other parameters are optional and will be automatically generated if not set.

For the spreadsheet resource sample that we will be using for this tutorial, the result of the tutorial Update Google Sheets Cells Using Google Sheets API PHP Client will be used.

Duplicate a sheet of a Google Spreadsheet using Google Sheets API PHP Client is the goal of this tutorial.

Requirements:

Step 1.

Prepare the needed parameters.

$spreadsheetId

The $spreadsheetId parameter.

This can be found in a Google Sheets URL, it contains a unique value of letters, numbers, hyphens, or underscores.

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';

php

The $duplicateSheetRequest parameter.

These are the values of the request that will duplicate a google sheets sheet.

/**
 * Set up parameters.
 */
$duplicateSheetRequest = [
    'duplicateSheet'   => [
        'sourceSheetId' => 0,
        'insertSheetIndex'  => 1,
        'newSheetId'    => '1234',
        'newSheetName'  => 'Duplicate Sheet'
    ],
];

Where.

sourceSheetId – required, this is the sheet to duplicate. This is the value of gid= on the google sheets url.
insertSheetIndex – optional, this is the zero-based incrementing index where the new sheet should be inserted. Duplicate sheet will be inserted in front of the source sheet if not set.
newSheetId – optional, this is the sheet ID of the new duplicate sheet, auto ID will be generated if not set.
newSheetName – optional, this is the sheet Name of the new duplicate sheet, auto Name will be generated if not set.

Step 2.

Inside your working directory, create a file (duplicate-google-sheets-sheet.php), and copy/paste the following codes. The paremeters from Step 1 are already included below.

<?php

// Autoload Composer.
require __DIR__ . '/vendor/autoload.php';

if (php_sapi_name() != 'cli') {
    throw new Exception('This application must be run on the command line.');
}

use Google\Client;
use Google\Service\Sheets\ValueRange;

/**
 * Returns an authorized API client.
 * @return Client the authorized client object.
 */
function getClient()
{
    $client = new Google\Client();
    $client->setApplicationName('Google Sheets API PHP Quickstart');
    $client->setScopes('https://www.googleapis.com/auth/spreadsheets');
    $client->setAuthConfig('credentials.json');
    $client->setAccessType('offline');
    $client->setPrompt('select_account consent');

    // Load previously authorized token from a file, if it exists.
    // The file token.json stores the user's access and refresh tokens, and is
    // created automatically when the authorization flow completes for the first
    // time.
    $tokenPath = 'token.json';
    if (file_exists($tokenPath)) {
        $accessToken = json_decode(file_get_contents($tokenPath), true);
        $client->setAccessToken($accessToken);
    }

    // If there is no previous token or it's expired.
    if ($client->isAccessTokenExpired()) {
        // Refresh the token if possible, else fetch a new one.
        if ($client->getRefreshToken()) {
            $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
        } else {
            // Request authorization from the user.
            $authUrl = $client->createAuthUrl();
            printf("Open the following link in your browser:\n%s\n", $authUrl);
            print 'Enter verification code: ';
            $authCode = trim(fgets(STDIN));

            // Exchange authorization code for an access token.
            $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
            $client->setAccessToken($accessToken);

            // Check to see if there was an error.
            if (array_key_exists('error', $accessToken)) {
                throw new Exception(join(', ', $accessToken));
            }
        }
        // Save the token to a file.
        if (!file_exists(dirname($tokenPath))) {
            mkdir(dirname($tokenPath), 0700, true);
        }
        file_put_contents($tokenPath, json_encode($client->getAccessToken()));
    }
    return $client;
}

function duplicateSheet($spreadsheetId, $duplicateSheetRequest)
{
    $client = getClient();
    $service = new Google_Service_Sheets($client);
    try{
        $requests = [
            new Google_Service_Sheets_Request($duplicateSheetRequest)
        ];

        $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
            'requests' => $requests
        ]);

        $response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);

        echo 'Duplicating of sheet of Spreadsheet ID ' . $response->getSpreadsheetId() . ' was successful.';

        return $response;
    }
    catch(Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' .$e->getMessage();
    }
}

/**
 * Set up parameters.
 */
$spreadsheetId = 'Your spreadsheetId here.';

/**
 * Set up parameters.
 */
$duplicateSheetRequest = [
    'duplicateSheet'   => [
        'sourceSheetId' => 0,
        'insertSheetIndex'  => 1,
        'newSheetId'    => '1234',
        'newSheetName'  => 'Duplicate Sheet'
    ],
];

/**
 * Attempt to update spreadsheet.
 */
duplicateSheet($spreadsheetId, $duplicateSheetRequest);

duplicate-google-sheets-sheet.php

Note. Each request is validated before being applied. Therefore, if any request is not valid then the entire request will fail and nothing will be applied

Step 3.

Execute the following command to perform the updating of cells of the Google Spreadsheet.

$ php duplicate-google-sheets-sheet.php

command line

If successful, the response will be like below.

$ php duplicate-google-sheets-sheet.php
Duplicating of sheet of Spreadsheet ID 'Your spreadsheetId here.' was successful.

Done.

We have made a sheet duplicate of a Google Spreadsheet from the command line in PHP using Google Sheets API PHP Client Library.

Result.

Before.

After.