Google脚本:请求失败并返回了代码500 [英] Google Script: Failed request and returned code 500

问题描述

我遇到一个称为 code 500 的错误.如果我在所有者帐户中使用脚本，该脚本可以正常工作，但是如果我要以用户/编辑者身份打开该文件，则会显示 code 500 错误.这是

这是代码

  function doGet(e){this [e.parameter.run](e.parameter.sheetName || null);return ContentService.createTextOutput('It work！');}函数HideRows(){const activeSheet = SpreadsheetApp.getActiveSheet();const url = ScriptApp.getService().getUrl();UrlFetchApp.fetch(url +?run = script_HideRows& sheetName =""+ activeSheet.getSheetName()，{标题:{授权:承载者" + ScriptApp.getOAuthToken()}})；//DriveApp.getFiles()//用于自动检测"https://www.googleapis.com/auth/drive.readonly"的范围.该范围用于访问令牌.}函数showRows(){const url = ScriptApp.getService().getUrl();UrlFetchApp.fetch(url +?run = script_showRows"，{标题:{授权:承载者" + ScriptApp.getOAuthToken()}})；Browser.msgBox(URL +?run = script_showRows");}var startRow = 6;var colToCheck = 2;//此脚本与您的"HideRows"相同.函数script_HideRows(){var sheetNames = ["MS_Q1"，"MS_Q2"，"MS_Q3"，"MS_Q4"，"SUMMARY"]；//请在此处设置工作表名称.在这种情况下，将使用4张纸.var ss = SpreadsheetApp.getActiveSpreadsheet();ss.getSheets().forEach(sheet => {var sheetName = sheet.getSheetName();如果(sheetNames.includes(sheetName)){if(sheetName =="SUMMARY"){//当工作表为"SUMMARY"时，开始行将更改.startRow = 7;}var numRows = sheet.getLastRow();var elements = sheet.getRange(startRow，colToCheck，numRows).getValues();for(var i = 0; i< elements.length; i ++){如果(shouldHideRow(sheet，i，elements [i] [0])){sheet.hideRows(startRow + i);}}//隐藏其余行var totalNumRows = sheet.getMaxRows();如果(totalNumRows> numRows)sheet.hideRows(numRows + 1，totalNumRows-numRows);}});}//此脚本与您的"showRows"相同.函数script_showRows(){//设置电子表格和表格var ss = SpreadsheetApp.getActiveSpreadsheet();//var ss = SpreadsheetApp.getActiveSpreadsheet()，varsheets = ss.getSheets();for(var i = 0，iLen = sheets.length; i< iLen; i ++){//获取工作表var sh = sheets [i];//取消隐藏列var rCols = sh.getRange("1:1");sh.unhideColumn(rCols);//取消隐藏行var rRows = sh.getRange("A:A");sh.unhideRow(rRows);}};函数shouldHideRow(ss，rowIndex，rowValue){如果(rowValue！='')返回false;如果(ss.getRange(startRow + rowIndex，colToCheck，1，1).isPartOfMerge())返回false;如果(ss.getRange(startRow + rowIndex + 1，colToCheck，1，1).isPartOfMerge())返回false;返回true；} 

解决方案

首先，对此进行注释

 //此[e.parameter.run](e.parameter.sheetName || null)； 

第二，避免这种情况

  const url = ScriptApp.getService().getUrl(); 

替换为

  const url ='https://script.google.com/macros/s/ABCD1234/exec'; 

第三，每次更改代码时，发布供所有用户访问的网络应用

下一个代码对我来说很好

  function doGet(e){return ContentService.createTextOutput('It work！');}函数HideRows(){const activeSheet = SpreadsheetApp.getActiveSheet();const url ='https://script.google.com/macros/s/ABCD1234/exec';var response = UrlFetchApp.fetch(url +'?run = script_HideRows& sheetName ='+ activeSheet.getSheetName()，{标头:{授权:'Bearer'+ ScriptApp.getOAuthToken()}，MutantHttpExceptions:是的，});Browser.msgBox(response);} 

I'm experiencing an error called code 500. The script works fine if I'm using it in the owner account, but if I'm going to open the file as a user/editor, the code 500 error shows. Here is the link to the sample spreadsheet that I'm working on. I tried asking here but seems like it is a little complicated so I created a new single spreadsheet so that it can be easily identified the error.

Here's the code

function doGet(e) {
  this[e.parameter.run](e.parameter.sheetName || null);
  return ContentService.createTextOutput('It worked!');
}

function HideRows() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url + "?run=script_HideRows&sheetName=" + activeSheet.getSheetName(), {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});
// DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}

function showRows() {
  const url = ScriptApp.getService().getUrl();
  UrlFetchApp.fetch(url + "?run=script_showRows", {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});
  Browser.msgBox(url + "?run=script_showRows");
}

var startRow = 6;
var colToCheck = 2;

// This script is the same with your "HideRows".
function script_HideRows() {
  var sheetNames = ["MS_Q1", "MS_Q2", "MS_Q3", "MS_Q4", "SUMMARY"];  // Please set the sheet names here. In this case, 4 sheets are used.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(sheet => {
    var sheetName = sheet.getSheetName();
    if (sheetNames.includes(sheetName)) {
      if (sheetName == "SUMMARY") {  // When the sheet is "SUMMARY", the start row is changed.
        startRow = 7;
      }
      var numRows = sheet.getLastRow();
      var elements = sheet.getRange(startRow, colToCheck, numRows).getValues();
     
      for (var i=0; i < elements.length; i++) {
        if (shouldHideRow(sheet, i, elements[i][0])) {
          sheet.hideRows(startRow + i);
        }
      }
      // Hide the rest of the rows
      var totalNumRows = sheet.getMaxRows();
      if (totalNumRows > numRows)
        sheet.hideRows(numRows+1, totalNumRows - numRows);
    }
  });
}

// This script is the same with your "showRows".
function script_showRows() {
  // set up spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
//  var ss = SpreadsheetApp.getActiveSpreadsheet(),
  var sheets = ss.getSheets();

  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    // get sheet
    var sh = sheets[i];

    // unhide columns
    var rCols = sh.getRange("1:1");
    sh.unhideColumn(rCols);

    // unhide rows
    var rRows = sh.getRange("A:A");
    sh.unhideRow(rRows);
  }
};

function shouldHideRow(ss, rowIndex, rowValue) {
  if (rowValue != '') return false;
  if (ss.getRange(startRow + rowIndex, colToCheck, 1, 1).isPartOfMerge()) return false;
  if (ss.getRange(startRow + rowIndex + 1, colToCheck, 1, 1).isPartOfMerge()) return false;
  return true;
}

解决方案

First, comment out this

// this [e.parameter.run] (e.parameter.sheetName || null);

Second, avoid this

const url = ScriptApp.getService().getUrl();

replace to

const url = 'https://script.google.com/macros/s/ABCD1234/exec';

Third, publish the web app for all user accessing every time you change code

The next code works for me fine

function doGet(e) {
  return ContentService.createTextOutput('It worked!');
}

function HideRows() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const url =
    'https://script.google.com/macros/s/ABCD1234/exec';
  var response = UrlFetchApp.fetch(
    url + '?run=script_HideRows&sheetName=' + activeSheet.getSheetName(),
    {
      headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true,
    }
  );

  Browser.msgBox(response);
}

这篇关于Google脚本:请求失败并返回了代码500的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！