Google Sheets API v4:添加命名或受保护的范围 [英] Google Sheets API v4: Add named or protected ranges

问题描述

在[[link]] [1]处使用了代码示例，因此无法从Google Apps脚本获取可用于将命名/受保护范围添加到Google Sheet的Google Sheets API v4代码.它给出了错误(正确的json格式是什么?):

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?):

接收到无效的JSON有效负载.未知名称"requests":找不到字段. (第5行，文件代码")

Invalid JSON payload received. Unknown name "requests": Cannot find field. (line 5, file "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

推荐答案

我认为您的请求正文正确.那么如何进行如下修改?

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

Sheets.Spreadsheets.Values.batchUpdate(

收件人:

Sheets.Spreadsheets.batchUpdate(

参考:

• 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.

"requests [0] .add_named_range.named_range.range.sheet_id"(TYPE_INT32)中的值无效，工作表ID"无效

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

从您的错误消息中，发现您使用sheet id作为工作表ID.因此，请将工作表ID从sheet id修改为正确的ID.

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.

如果要手动获取工作表ID.请此处.

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

如果要使用脚本检索工作表ID，该怎么办?

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.

或

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

这篇关于Google Sheets API v4:添加命名或受保护的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！