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-15T05:42:20.883

5

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.

edited Dec 15 '18 at 05:42

answered Dec 14 '18 at 07:08

Tanaike

181,128
11
97
165

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 Dec 14 '18 at 11:34
@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``. – Tanaike Dec 14 '18 at 23:08
@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 Dec 15 '18 at 05:42
@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 Dec 15 '18 at 05:51
@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](https://stackoverflow.com/questions/53774415/how-to-protect-a-sheet-then-unprotect-specific-cells/53775476#53775476) 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. – Tanaike Dec 15 '18 at 05:57
Range needs to be protected from changes if accessed from Spreadsheet and open for changes only from an interface (like a web app built with Google Apps Script) using the API first to unprotect the range, make changes and then protect the range from changes again. Another problem is the spreadsheet has filters that stops working with protected ranges. – Din Dec 15 '18 at 06:03
@Din I'm sorry. I might be able to not understand correctly your situation about ``the spreadsheet has filters that stops working with protected ranges.``. Can you post it by including more information as new question? I would like to try to understand your situation. – Tanaike Dec 15 '18 at 06:11

Google Sheets API v4: Add named or protected ranges

1 Answers1

From:

To:

Reference:

Edit:

Linked