Google Sheets API v4: Add named or protected range

问题:

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

回答1:

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.