将Google表格标签锁定在适当的位置 [英] Lock Google Sheet tabs in place

问题描述

是否可以为用户提供对Google工作表的编辑访问权限，但阻止他们更改底部工作表标签的顺序?

Is it possible to give users edit access to a google sheet, but prevent them from changing the order of the sheet tabs at the bottom?

致谢

推荐答案

您不能阻止工作表被编辑人员重新排序，但是您当然可以将其重新排序.如果有人查看可用的电子表格事件，则可能会认为更改时"事件将触发重新订购的工作表.截至2019年1月，情况并非如此.但是，您仍然可以使用已安装的触发器绑定多个事件(即更改"，编辑"和打开")，并通过使用

You cannot prevent sheets from being reordered by editors, but you can certainly reorder them back. If one reviews the available Spreadsheet events, one might think that the "on change" event will fire for re-ordered sheets. As of Jan 2019, this is not true. However, you can still bind multiple events (i.e. "change", "edit", and "open") with an installed trigger, and enforce your desired sheet ordering through the use of either the Spreadsheet Service or the advanced service Sheets, i.e. the Sheets REST API.

最有效的重新排序是通过Sheets API完成的，而无需修改用户的活动工作表，但是如果您每个API调用对单个工作表的重新排序次数超过一个，则它会出现UI刷新错误(重新排序发生在Google端，而浏览器用户界面未更新).每个API调用平均可以在大约100-230毫秒内完成重新排序.

The most efficient reordering is done with the Sheets API without modifying the users' active sheets, but it has a UI refresh bug if you reorder more than a single sheet per API call (the reordering occurs on Google's end, and the browser UI(s) are not updated). This reordering can be done in an average of ~100-230 ms per API call.

以下示例代码并不是API效率最高的示例，目的是更易于理解和实现:

Here is example code that is not the most API-efficient, in order to be simpler to understand and implement:

function enforceOrder(eventObject) {
  const requiredSheetOrder = ['name of first sheet', 'name of second sheet' ....];
  // Get the workbook ID for the Sheets REST API.
  const id = (eventObject ? eventObject.source : SpreadsheetApp.getActive()).getId();
  // You must enable the advanced service prior to using this code.
  // https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services
  const state = Sheets.Spreadsheets.get(id, {fields: "sheets/properties(title,sheetId,index)"});

  // Collect the requests to be made.
  const batchRequests = [];
  requiredSheetOrder.forEach(function (title, i) {
    var rq = {
      fields: "index",
      properties: { index: i }
    };
    var matched = state.sheets.filter(function (s) { return s.properties.title === title; })[0];
    if (matched)
    {
      rq.properties.sheetId = matched.properties.sheetId;
      // If any preceding sheets are being reordered, or this sheet
      // is not in the right position, we must set this sheet's index.
      if (batchRequests.length || i !== matched.properties.index)
        batchRequests.push({ updateSheetProperties: rq });
    }
    else
      console.warn("No sheet found with required name '" + title + "'");
  });

  // Send updates, if there were any to send.
  if (batchRequests.length)
  {
    // Sheets.Spreadsheets.batchUpdate({ requests: batchRequests }, id);
    // The above wholly-batch line induces the mentioned UI bug.
    // The below one-by-one update does not:
    batchRequests.forEach(function (r) {
      Sheets.Spreadsheets.batchUpdate({ requests: [r] }, id);
    });
    console.log("Reordered " + batchRequests.length + " sheets");
  }
  else
    console.log({message: "No-op", desired: requiredSheetOrder, current: state.sheets });
}

可以通过比较当前工作表状态和所需结果状态，并计算最小索引规格来确定工作表顺序(即，考虑将工作表放置在索引i处移动)来提高API效率当前索引为i到i+1的工作表).

API efficiency can be improved by comparing the current sheet state and the desired outcome state, and computing the minimum number of index specifications to fix the sheet ordering (that is, taking into account that placing a sheet at index i moves the sheet currently at index i to i+1).

PS:使用电子表格服务时，电子表格的位置/电子表格索引基于1.当使用高级服务/REST API时，工作表的位置索引始终为0基.

PS: When working with the Spreadsheet Service, the position / spreadsheet index of a sheet is 1-based. When working with the advanced service / REST API, the sheet's position index is always 0-base.

这篇关于将Google表格标签锁定在适当的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！