我遇到一个名为的错误code 500
。如果我在所有者帐户中使用脚本,该脚本可以正常运行,但是如果我要以用户/编辑者身份打开该文件,则会code 500
显示错误。这是我正在处理的示例电子表格的链接。我尝试在这里询问,但似乎有点复杂,所以我创建了一个新的单个电子表格,以便可以轻松识别错误。
这是代码
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;
}
首先,将其注释掉
// this [e.parameter.run] (e.parameter.sheetName || null);
其次,避免这种情况
const url = ScriptApp.getService().getUrl();
替换为
const url = 'https://script.google.com/macros/s/ABCD1234/exec';
第三,每次更改代码时,发布供所有用户访问的Web应用程序
下一个代码对我有用
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);
}
这部分
const url = 'https://script.google.com/macros/s/ABCD1234/exec';
是Web应用程序的URL,对吗?是的!这是您的Web应用程序公共端点。不是开发人员。
发生错误
Exception: Request failed for https://script.google.com returned code 401.
检查两次。该论坛不用于调试其他人的代码。我肯定知道我得到了工作结果。唯一的区别在于我指出的那些代码行。
抱歉!我会解决这个的。感谢您的帮助!