How to protect range in Google Sheets using API v4 in PHP?

Question

I am working on making some voting collection statistics sheets programmatically. The next step fro me is figuring out how to protect certain cells using the API from being edited. In this example, I actually want to go ahead and protect all of the first three columns. I have been unable to find documentation for this. Any assistance is greatly appreciated.

<?php
/*
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

include_once __DIR__ . '/includes/google/vendor/autoload.php';

$client = getClient();

$service = new Google_Service_Sheets($client);

// TODO: Assign values to desired properties of `requestBody`:
//$requestBody = new Google_Service_Sheets_Spreadsheet();
$spreadsheet = new Google_Service_Sheets_Spreadsheet([
    'properties' => [
        'title' => 'US Senate 2'
    ]
]);

$spreadsheet = $service->spreadsheets->create($spreadsheet, [
    'fields' => 'spreadsheetId'
]);

$fileId=$spreadsheet->spreadsheetId;

echo $fileId;


// TODO: Change code below to process the `response` object:
echo '<pre>', var_export($spreadsheet, true), '</pre>', "\n";


//Give permissions
/*
$client2 = new \Google_Client();
$client2->setApplicationName('Give permissions');
$client2->setScopes([\Google_Service_Drive::DRIVE]);
$client2->setAccessType('offline');
$client2->setAuthConfig('../credentials.json');
$client2->setPrompt('select_account consent');
$service2 = new Google_Service_Drive($client2);
$newPermission = new Google_Service_Drive_Permission();
$newPermission->setEmailAddress("user@example.com");
$newPermission->setType('user');
$newPermission->setRole('writer');
$fileId=$spreadsheet->spreadsheetId;
$service2->permissions->create($fileId, $newPermission);
*/


//Add data from the dataTables
$client3 = new \Google_Client();
$client3->setApplicationName('Add data from datatables');
$client3->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client3->setAccessType('offline');
$client3->setAuthConfig('credentials.json');
$tokenPath = 'token.json';
if (file_exists($tokenPath)) {
    $accessToken = json_decode(file_get_contents($tokenPath), true);
    $client3->setAccessToken($accessToken);
}
$service3 = new Google_Service_Sheets($client3);
$spreadsheetId = $fileId;
$range = 'Sheet1';


$headers = array(
  "Candidate",
  "Election",
  "Id",
  "Votes"
);

$row1[]="Collins";
$row1[]="US Senate";
$row1[]="1010010";
$row1[]="0";

$values = [
    $headers,
    $row1,
    // Additional rows ...
];

$body = new Google_Service_Sheets_ValueRange([
    'values' => $values
]);
$params = [
    'valueInputOption' => 'RAW'
];
$insert = [
    "insertDataOption" => "INSERT_ROWS"
];
$result = $service3->spreadsheets_values->append(
    $spreadsheetId,
    $range,
    $body,
    $params,
    $insert
);
echo "<a href='https://docs.google.com/spreadsheets/d/".$fileId."' target='_blank'>https://docs.google.com/spreadsheets/d/".$fileId."</a>";


function getClient()
{
    $redirect_uri = 'http://localhost:8000/' . $_SERVER['PHP_SELF'];

    $client = new Google_Client();
    $client->setApplicationName('Google Sheets API PHP Quickstart');
    //$client->setScopes(Google_Service_Sheets::SPREADSHEETS_READONLY);
    $client->addScope("https://www.googleapis.com/auth/drive");
    $client->addScope("https://www.googleapis.com/auth/drive.file");
    $client->addScope("https://www.googleapis.com/auth/spreadsheets");
    $client->setRedirectUri($redirect_uri);
    $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;
}

Answer 1

I believe your goal and your situation as follows.

• You want to protect the columns "A", "B" and "C" of a sheet in Google Spreadsheet using googleapis for php.
• You can get and put values for Google Spreadsheet using Sheets API.

Modification point:

• In this case, please use "addProtectedRange" with the method of "spreadsheets.batchUpdate" in Sheets API.

Modified script:

$spreadsheetId = "###"; // please set Spreadsheet ID.
$sheetId = "###"; // Please set sheet ID.
$requests = [
    new Google_Service_Sheets_Request([
        'addProtectedRange' => [
            'protectedRange' => [
                'range' => [
                    'sheetId' => $sheetId,
                    'startRowIndex' => 0,
                    'startColumnIndex' => 0,
                    'endColumnIndex' => 3,
                ],
                'description' => 'sample description'
            ]
        ]
    ])
];
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
  'requests' => $requests
]);
$result = $service3->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);

• When above script is run, the columns "A" to "C" of $sheetId in $spreadsheetId are protected.

Note:

• $service3 is from your script.
• Please set the range as the GridRange. Ref
• In above script, the editor is the owner.

Reference:

• Method: spreadsheets.batchUpdate
• AddProtectedRangeRequest
• GridRange