Google spreadsheet, how to protect Cell?

Question

I create excel file and proteced some field in this file. In Office excel all fine my some cell locked for edit, begin I upload my excel file in google drive with convert - true (Whether to convert this file to the corresponding Google Docs format. (Default: false)) But in google spread sheet I can edit locked cell, how to locked in google spread sheet some cell ?

create excel file

    $phpExcel = new \PHPExcel();
    $ews = $phpExcel->getSheet(0);
    $ews->setTitle(Reports::LIST_AOG);
    $ews->getProtection()->setSheet(true);
    $ews
        ->getStyle('E6:F36')
        ->getProtection()->setLocked(
            \PHPExcel_Style_Protection::PROTECTION_UNPROTECTED
        );

and now I can edit only E6:F36 cell in my file in computer, then upload in google drive

$insertArray = [
        'mimeType' => $fileUpload->getMimeType(),
        'uploadType' => 'media',
        'data' => file_get_contents($fileUpload),
        'convert' => true
    ];

    $service = new \Google_Service_Drive($client->getGoogleClient());
    $file = new \Google_Service_Drive_DriveFile();

    $file->setTitle($nameFile);
    $file->setMimeType($fileUpload->getMimeType());

    try {
        $createdFile = $service->files->insert($file, $insertArray);
        $spreadsheetId = $createdFile->getId();
    } catch (\Exception $e) {
        $view = $this->view((array)GoogleDriveController::ERROR_OCCURRED . $e->getMessage(), 400);
        return $this->handleView($view);
    }

I fing for google spreadsheet api bundle asimlqt/php-google-spreadsheet-client but not find how to protected

    $serviceRequest = new DefaultServiceRequest($arrayAccessTokenClient['access_token']);
    ServiceRequestFactory::setInstance($serviceRequest);

    $spreadsheetService = new SpreadsheetService();

    $spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheetId);
    $worksheetFeed = $spreadsheet->getWorksheets();
    $cellFeed = $worksheet->getCellFeed();

    $cellFeed->editCell(1, 1, 'developer');
    $cellFeed->editCell(1, 2, 'hors');
    $cellFeed->editCell(10, 2, 'sum');

or how to protected cell with asimlqt/php-google-spreadsheet-client ?

and In google spread sheet I can any edit any cell (((( Who knows hot to protected cell in google spreat sheet ?

UPDATE

I read Google Sheets API and try create request, this I have

    $arrayAccessTokenClient = json_decode($client->getGoogleClient()->getAccessToken(), true);

    $serviceRequest = new DefaultServiceRequest($arrayAccessTokenClient['access_token']);
    ServiceRequestFactory::setInstance($serviceRequest);

    $spreadsheetService = new SpreadsheetService();

    $spreadsheet = $spreadsheetService->getSpreadsheetById($spreadsheetId);
    $worksheetFeed = $spreadsheet->getWorksheets();
    $worksheet = $worksheetFeed->getByTitle(Reports::LIST_AOG);


        $addProtectedRange['addProtectedRange'] = [
        'protectedRange' => [
            'range' => [
                'sheetId' => $worksheet->getGid(),
                'startRowIndex' => 3,
                'endRowIndex' => 4,
                'startColumnIndex' => 0,
                'endColumnIndex' => 5,
            ],
            'description' => "Protecting total row",
            'warningOnly' => true
        ]
    ];
    $guzzle = new Client();
    $putTeam = $guzzle
        ->post('https://sheets.googleapis.com/v4/spreadsheets/'.$spreadsheetId.':batchUpdate?key='.$arrayAccessTokenClient['access_token'],
            [],
            json_encode($addProtectedRange)
        )
        ->send()
        ->getBody(true);
    $answer = json_decode($putTeam, true);

But have

Client error response
[status code] 401
[reason phrase] Unauthorized
[url]  https://sheets.googleapis.com/v4/spreadsheets/1M_NFvKMZ7Rzbj9ww86AJRMto1UesIy71840r2sxbD5Y:batchUpdate?key=myAccessToken

Early I have Google Api Clien with access token and I can change cell and update with google spread sheet and work fine but https://sheets.googleapis.com/v4/spreadsheets/'.$spreadsheetId.':batchUpdate?key='.$arrayAccessTokenClient['access_token'],

return 401 and I not understand why and how to correct. Help

score 0 · Answer 1 · edited Mar 17 '17 at 13:14

0

I think google spreadsheets allow the editing of protected MSExcel sheets the moment they become Google docs. The rules for MSExcel doesn't always apply to Google Sheets. Reading from Adding Named or Protected Ranges:

The following spreadsheets.batchUpdate request contains two request objects. The first gives the range A1:E3 the name "Counts". The second provides a warning-level protection to the range A4:E4. This level protection still allows cells within the range to be edited, but prompts a warning prior to making the change.

This thread also shares the same view.

However, if it's a google spreadsheet file you want to protect, check this guide.

edited Mar 17 '17 at 13:14

Community

1
1

answered Aug 02 '16 at 08:29

ReyAnthonyRenacia

17,219
5
37
56

Let's start with this. 'sheetId' is not 'spreadsheetId'. sheetId is the number following 'gid' like gid=1234567 in your URL. In this instance, 1234567 is the sheetId – ReyAnthonyRenacia Aug 02 '16 at 10:11
Update question. I replace to `$worksheet->getGid()` But still have 410 Unauthorized – shuba.ivan Aug 02 '16 at 11:53
why Unauthorized, if with this `$arrayAccessTokenClient['access_token']` I edit cell and every this all fine – shuba.ivan Aug 02 '16 at 11:55
since you're using Sheets API, it must be enabled – ReyAnthonyRenacia Aug 02 '16 at 12:09
in my app Sheets API enabled, and with `asimlqt/php-google-spreadsheet-client` I can edit cell in spreadsheet and every thins fine – shuba.ivan Aug 02 '16 at 12:11
go to oauth playground get yourself an oauth token from sheetsv4, allow permission , generate token and try to use that as access_token. if it works, then u know where the problem is. – ReyAnthonyRenacia Aug 02 '16 at 13:27
I try in Playgriund, and all good, I see request `Authorization: Bearer ya29.Ci80AxlHEzPA0nAfTW6aiM5ztjvX7_E2xIlI977HxX2bmJwhfB2df8ZeSACzgM_5pQ` maybe this problem, because I add `?key=myAccessToken` I try this and let you know – shuba.ivan Aug 03 '16 at 08:15
yes, use that as your 'access_token' .it only has 1 hr lifecycle. if it works, then you current access_token handling is the problem. – ReyAnthonyRenacia Aug 03 '16 at 09:14
Yes, when I add in headers `Authorization: Bearer ya29.Ci80AxlHEzPA0nAfTW6aiM5ztjvX7_E2xIlI977HxX2bmJwhfB2df8ZeSACzgM_5pQ` I have protected cell in google spread sheet. But uwer with role writer can edit this cell, now only add window for atention this is protected range, but I need protected for edit for all user – shuba.ivan Aug 03 '16 at 11:12

score 0 · Answer 2 · edited May 23 '17 at 12:09

I read documentation and

        $addProtectedRange['requests'] = [
        'addProtectedRange' => [
            'protectedRange' => [
                'range' => [
                    'sheetId' => $worksheetGid,
                    'startRowIndex' => $startRowIndex,
                    'endRowIndex' => $endRowIndex,
                    'startColumnIndex' => $startColumnIndex,
                    'endColumnIndex' => $endColumnIndex,
                ],
                'description' => "Protecting total row",
                'warningOnly' => false,
                'editors' => [
                    'users' => [
                        "shuba.ivan.vikt@gmail.com"
                    ]
]
            ],
        ]
    ];

This file_get_contents variant, because Guzzle not returned json response from google, like this:

{
 "protectedRangeId": number,
 "range": {
   object(GridRange)
 },
 "namedRangeId": string,
 "description": string,
 "warningOnly": boolean,
 "requestingUserCanEdit": boolean,
 "unprotectedRanges": [
   {
     object(GridRange)
   }
 ],
  "editors": {
   object(Editors)
 },
}

I create new question for this for Guzzle reasponse

This is variant for update protected edit cell

    $addProtectedRangeJson = json_encode($addProtectedRange);
    $url = 'https://sheets.googleapis.com/v4/spreadsheets/' . $spreadsheetId . ':batchUpdate';
    $opts = array('http' =>
        array(
            'method' => 'POST',
            'header' => "Content-type: application/json\r\n" .
                "Authorization: Bearer $arrayAccessTokenClient\r\n",
            'content' => $addProtectedRangeJson
        )
    );

    $context = stream_context_create($opts);
    $response = file_get_contents($url, FALSE, $context);
    $answer = json_decode($response, TRUE);

    $addProtectedRangeUpdate['requests'] = [
        'updateProtectedRange' => [
            'protectedRange' => [
                'protectedRangeId' => $protectedRangeId,
                'warningOnly' => false,
                'editors' => [
                    'users' => [
                        "shuba.ivan.vikt@gmail.com"
                    ]
                ]
            ],
            'fields' => "namedRangeId,warningOnly,editors"
        ]
    ];
    $addProtectedRangeUpdateJson = json_encode($addProtectedRangeUpdate);
    $optsUpdate = array('http' =>
        array(
            'method' => 'POST',
            'header' => "Content-type: application/json\r\n" .
                "Authorization: Bearer $arrayAccessTokenClient\r\n",
            'content' => $addProtectedRangeUpdateJson
        )
    );

    $contextUpdate = stream_context_create($optsUpdate);
    $responseUpdate = file_get_contents($url, FALSE, $contextUpdate);
    $answerUpdate = json_decode($responseUpdate, TRUE);

and this is Guzzle, but Guzzle without google json response this is bad I wait maybe who know what is it problem, because standard php function file_get_contents work very well and I have json google response. But without response this variant work normal, cell protected in spread sheet

    $guzzle = new Client();

    $postCell = $guzzle
        ->post('https://sheets.googleapis.com/v4/spreadsheets/' . $spreadsheetId . ':batchUpdate',
            [],
            $addProtectedRangeJson
        )
        ->addHeader('Authorization', 'Bearer ' . $arrayAccessTokenClient)
        ->addHeader('Content-type', 'application/json')
    ;
    $postCell
        ->send()
        ->getBody(true)
    ;
    $contents = (string) $postCell->getBody();// get body that I post -> my request, not response 
    $contents = $postCell->getBody()->getContents();// not find getContents, ask me did you mean to call getContentMd5
    $answer = json_decode($postCell->getResponse()->getBody(), true);

Google spreadsheet, how to protect Cell?

2 Answers2