当我尝试使用moveActiveSheet时Google脚本出现异常错误 [英] Exception Error with Google Script when I tried to use moveActiveSheet

问题描述

我不断收到例外:访问ID为ID的文档时，服务电子表格失败了"尝试运行moveActiveSheet将带有索引4的选项卡中的工作表移动到索引1时.这只是最近发生的事情.在发生这种情况之前，我已经使用我的代码几个月了.有人面对同一件事吗?

I keep getting "Exception: Service Spreadsheets failed while accessing document with id " while trying to run moveActiveSheet to move a sheet in tab with index 4 to index 1. This just happened very lately. I had been using my code for months before this happened. Does any body is facing the same thing?

我的代码在下面

var ss = SpreadsheetApp.getActiveSpreadsheet();
//==========================================
//Arrange the Sheet
// Store all the worksheets in this array
var sheetNameArray = [];
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
};
sheetNameArray.sort();

var sheetName = sheetNameArray[0]; //This tab is currently at index 4
var getSheet = ss.getSheetByName(sheetName);
Logger.log(getSheet.getIndex());
ss.setActiveSheet(getSheet);
ss.moveActiveSheet(1); //Trying to move it to index 1 but exception was thrown

推荐答案

这是对我有用的解决方法，我遍历了排序后的数组.我使用了 Sheets API 中的.batchUpdate.您必须先从高级服务启用它:Resources>进阶Google服务

Here is a work-around that worked for me, I loop through the sorted array. I used .batchUpdate from Sheets API. You have to enable it from advanced services first: Resources > Advanced Google Services

function move_sheet_func(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //==========================================
  //Arrange the Sheet
  // Store all the worksheets in this array
  var sheetNameArray = [];
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  };
  sheetNameArray.sort();
  
  var sheetName = sheetNameArray[0]; //This tab is currently at index 4
  var getSheet = ss.getSheetByName(sheetName);
  var sheetsreq = [];
  //create requests for each sheet to be moved to its index as sorted
  sheetNameArray.forEach(function (name, indx) {
    sheetsreq.push({ updateSheetProperties: {
      fields: "index, title",
      properties: {
        index: indx,
        title: name,
        sheetId: ss.getSheetByName(name).getSheetId()
      }
    } });
  });
  var req = { "requests": sheetsreq };
  console.log(JSON.stringify(req));
  Sheets.Spreadsheets.batchUpdate(req, ss.getId());

}

这篇关于当我尝试使用moveActiveSheet时Google脚本出现异常错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！