如何格式化Sheets.Spreadsheets.batchUpdate(resource，电子表格ID)的资源以创建列组 [英] How to format the resource for Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId) to create a column group

问题描述

• 我正在尝试创建一个列组.
• 我已经启用了Sheets API版本4.
• 我已经查看了 Sheets中的参考API

• 使用代码完成功能，我想出了以下代码:

• I'm trying to create a column group.
• I've enabled Sheets API version 4.
• I've looked at the reference in Sheets API

• Using code completion I've come up with this code:

function makeColumnGroup() { var ss=SpreadsheetApp.getActive(); var sh=ss.getSheetByName('Sheet21'); Sheets.Spreadsheets.batchUpdate({"range":[{dimension:"COLUMNS",startIndex:1,endIndex:3,sheetId:sh.getSheetId()}]},ss.getId()); }

function makeColumnGroup() { var ss=SpreadsheetApp.getActive(); var sh=ss.getSheetByName('Sheet21'); Sheets.Spreadsheets.batchUpdate({"range":[{dimension:"COLUMNS",startIndex:1,endIndex:3,sheetId:sh.getSheetId()}]},ss.getId()); }

我已经经历了几个不同的版本，但是就目前而言，我遇到了错误:

I've been through several different versions but as it stands now I'm getting the Error:

Invalid JSON payload received. Unknown name "range": Cannot find field. (line 4, file "20181212")

但是在API参考中，它表明JSON格式的AddDimensionGroupRequest看起来像这样:

But in the API reference it shows that the AddDimensionGroupRequest in JSON format looks like this:

{ "range": { object(DimensionRange) } }

{ "range": { object(DimensionRange) } }

DimensionRange看起来像这样:

the the DimensionRange looks like this:

{ "sheetId": number, "dimension": enum(Dimension), "startIndex": number, "endIndex": number }

{ "sheetId": number, "dimension": enum(Dimension), "startIndex": number, "endIndex": number }

代码完成建议的格式如下:

Code Completion suggests a format as follows:

Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId)

但是我似乎无法想出魔术公式来计算该资源的格式.过去的经验建议使用Drive API这样的格式:

but I can't seem to come up with the magic formula for figuring out the format for that resource. Past experiences suggest a format such as this with the Drive API:

Drive.Files.update({"parents": [{"id": subFldr.getId()}]}, res.getId());

但是，如果我使用Sheets API建议的格式，则此方法将无效:

But this is not working if I used the format suggested by the Sheets API:

Sheets.Spreadsheets.batchUpdate({"range":{dimension:"COLUMNS",startIndex:1,endIndex:3,sheetId:sh.getSheetId()}},ss.getId());

我得到了错误:

Invalid JSON payload received. Unknown name "range": Cannot find field. (line 4, file "20181212")

问题: 如何设置资源参数的格式?

The question: How do I format the resource parameter?

推荐答案

尽管我不确定这是否对您有用，但作为另一种方法，您也可以使用这些方法通过高级Google服务表格创建请求.当您尝试在脚本编辑器上使用高级Google服务表格时，通过自动完成功能，您可能会看到一些方法.使用这种方法时，可以通过脚本编辑器的自动完成来知道所需的参数.因此，我认为这将使我们轻松创建请求.作为示例，可以像此示例一样创建答案中的请求正文.

Although I'm not sure whether this is useful for you, as another method, you can also use the methods for creating requests with Sheets of Advanced Google Service. When you try to use Sheets of Advanced Google Service on the script editor, you might see some methods by the auto completion. When such methods are used, the required parameters can be known by the auto completion with the script editor. So I think that it will make us easily create the requests. As a sample, the request body in your answer can be created like this sample.

function makeColumnGroup() {
  var ss=SpreadsheetApp.getActive();

  // Set parameters of addDimensionGroup
  var p = Sheets.newAddDimensionGroupRequest();
  p.range = Sheets.newGridRange();
  p.range.dimension = "COLUMNS";
  p.range.startIndex = 1;
  p.range.endIndex = 3;
  p.range.sheetId = ss.getSheetId();

  // Create single request
  var req = Sheets.newRequest();
  req.addDimensionGroup = p;

  // Create batch requests
  var batchReq = Sheets.newBatchUpdateSpreadsheetRequest();
  batchReq.requests = [req];

  // Request
  Sheets.Spreadsheets.batchUpdate(batchReq, ss.getId());
}

注意:

• 确认batchReq后，发现它与您的请求正文相同.
• 很遗憾，我找不到与这些方法有关的官方文档.我认为这可能是假设用户通过自动完成功能找到了方法.

Note:

• When batchReq is confirmed, it is found that it is the same with your request body.
• Unfortunately, I couldn't find the official documents related to these methods. I thought that it might suppose that users find the methods by the auto completion.

例如，当创建一个组时，为了折叠该组，该示例脚本如何?在此示例脚本中，它创建了addDimensionGroup和updateDimensionGroup的请求.这些请求由batchUpdate运行.

For example, when a group is created, in order to collapse the group, how about this sample script? In this sample script, it creates the requests of addDimensionGroup and updateDimensionGroup. These requests are run by batchUpdate.

function makeColumnGroup() {
  var ss=SpreadsheetApp.getActive();

  // Set range
  var range = Sheets.newGridRange();
  range.dimension = "COLUMNS";
  range.startIndex = 1;
  range.endIndex = 3;
  range.sheetId = ss.getSheetId();

  // Create request of addDimensionGroup
  var p1 = Sheets.newAddDimensionGroupRequest();
  p1.range = range;
  var req1 = Sheets.newRequest();
  req1.addDimensionGroup = p1;

  // Create request of updateDimensionGroup
  var p2 = Sheets.newUpdateDimensionGroupRequest();
  p2.dimensionGroup = Sheets.newDimensionGroup();
  p2.dimensionGroup.collapsed = true;
  p2.dimensionGroup.depth = 1;
  p2.dimensionGroup.range = range;
  p2.fields = "*";
  var req2 = Sheets.newRequest();
  req2.updateDimensionGroup = p2;

  // Create batch requests
  var batchReq = Sheets.newBatchUpdateSpreadsheetRequest();
  batchReq.requests = [req1, req2];

  // Request
  Sheets.Spreadsheets.batchUpdate(batchReq, ss.getId());
}

这篇关于如何格式化Sheets.Spreadsheets.batchUpdate(resource，电子表格ID)的资源以创建列组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！