Google Sheets API v4: Add named or protected ranges

Question

Can't get the Google Sheets API v4 code working from Google Apps Script for adding named/protected ranges to the Google Sheet, used the code sample from here [[link]][1]. It gives the error (what is the correct json form for this?):

Invalid JSON payload received. Unknown name "requests": Cannot find field. (line 5, file "Code")

Below is the code:

function protectSheet() { var sheetId = "sheet id"; var spreadsheetId = "spreadsheet id"; Sheets.Spreadsheets.Values.batchUpdate( { "requests": [ { "addNamedRange": { "namedRange": { "name": "Counts", "range": { "sheetId": sheetId, "startRowIndex": 0, "endRowIndex": 3, "startColumnIndex": 0, "endColumnIndex": 5, }, } } }, { "addProtectedRange": { "protectedRange": { "range": { "sheetId": sheetId, "startRowIndex": 3, "endRowIndex": 4, "startColumnIndex": 0, "endColumnIndex": 5, }, "description": "Protecting total row", "warningOnly": true } } } ] }, spreadsheetId); } [1]: https://developers.google.com/sheets/api/samples/ranges

Tanaike · Accepted Answer · 2018-12-15 05:42:20Z

I think that your request body is correct. So how about modifying as follows?

From:

Sheets.Spreadsheets.Values.batchUpdate(

To:

Sheets.Spreadsheets.batchUpdate(

Reference:

spreadsheets.batchUpdate

If you have any issues for the request body, please tell me. I would like to think of the issues.

Edit:

Invalid value at 'requests[0].add_named_range.named_range.range.sheet_id' (TYPE_INT32), "sheet id" Invalid

From your error message, it is found that you use sheet id as the sheet ID. So please modify the sheet ID from sheet id to the correct one.

If you want to manually retrieve the sheet ID. Please check here.

If you want to retrieve the sheet ID using script, how about this?

var spreadsheetId = "spreadsheet id"; // Please set spreadsheetId here. var ss = SpreadsheetApp.openById(spreadsheetId); var sheetId = ss.getSheetByName(sheetName).getSheetId(); // sheetName is the sheet name of each sheet.

or

var sheetId = ss.getSheets()[index].getSheetId(); // index is the index of sheet. This is start from 0.

changing Sheets.Spreadsheets.Values.batchUpdate to Sheets.Spreadsheets.batchUpdate it now gives the following error: Invalid value at 'requests[0].add_named_range.named_range.range.sheet_id' (TYPE_INT32), "sheet id" Invalid
@Din I'm really sorry for the inconvenience. I think that TheMaster's comment is correct. In your case, it is considered that the reason of issue is that sheetId is not correct. When sheetId is used as the wrong ID, such error occurs. And from your error, it can see "sheet id" Invalid. You use "sheet id" as the sheet ID. This is the issue. So can you confirm the correct sheet ID again? For example, at new Spreadsheet, the sheet ID of 1st sheet is 0.
@Din Because I'm not sure whether the error was removed, I updated my answer. Could you please confirm it? If the error is not removed, please tell me.
@Tanaike thank you very much. It's working. There is one more thing, changed "warningOnly": true to false trying to protect the range from changes but it still allows editing. Or is it better to post another question on this?
@Din Thank you for replying. If you want to allow to edit for only several ranges and protect other range, how about this process? 1. Protect a sheet. 2. Unprotect the ranges you want to allow to edit. I remembered that a recent question will be useful for your situation. I think that although SpreadsheetApp service is used in that question, the same situation can be achieved by Sheets API.

Collectives™ on Stack Overflow

Google Sheets API v4: Add named or protected ranges

1 Answer 1

From:

To:

Reference:

Edit:

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

Reference:

Edit:

7 Comments

Linked

Related