Re: [Apps-Script] Google Sheet - Exception: Request failed for https://script.google.com returned code 500

[Adam Morris]

So the first thing I'm noticing is that you might be that the code here doesn't have a script_showRows function. Line 2 will grab that function from the global scope, but if the function name is missing, you'll end up with a javascript error, hence the 500.

It might help you to reason about the code by outputting these values, such as e.paramater.run to a spreadsheet, before line 2. Also before line 2, output the result of this[e.paramter.run] which is probably undefined. That way you can inspect and see what's going on.

I also kinda wonder if there is a reason why you use its own script as a web app when you could just execute script_showRows directly? Or maybe you're just providing minimal code to reproduce it?

Adam

On Fri, Nov 27, 2020 at 2:05 PM cjvdg <it.is...@gmail.com> wrote:

Can someone help me with this?

There is an error on my sheet when I press the HIDE ROWS or SHOW ROWS button. The button works for multiple sheets as you can see on the codes below. I do have a main sheet DB_CONNECT wherein the buttons are located, then multiple sheets like MTB_Q1, MTB_Q2 and so on.. wherein it has a protected range or sheet and wherein the script that I'm using should work.

This is the error:

Exception: Request failed for https://script.google.com returned code 500. Truncated server response: <meta name="viewport" c... (use muteHttpExceptions option to examine full response)

Here's the code that I'm using

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

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()}});
}

var startRow = 11;
var colToCheck = 2;

// This script is the same with your "HideRows".
function script_HideRows() {
  var sheetNames = ["MTB_Q1", "MTB_Q2", "MTB_Q3", "MTB_Q4", "SUMMARY OF QUARTERLY GRADES"];  // 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 OF QUARTERLY GRADES") {  // When the sheet is "SUMMARY", the start row is changed.
        startRow = 12;
      }
      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);
    }
  }); 

}

What seems to be the problem here? The error only shows when I'm using it as an editor/user, but as an owner, it works fine. 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/90072907-52b6-4708-9ffe-405bcb9fd322o%40googlegroups.com.

[cjvdg]

Hello! I edit it and put the whole code there. The code comes from here. Can you help me?

On Friday, November 27, 2020 at 2:27:00 PM UTC+8, Adam Morris wrote:

So the first thing I'm noticing is that you might be that the code here doesn't have a script_showRows function. Line 2 will grab that function from the global scope, but if the function name is missing, you'll end up with a javascript error, hence the 500.

It might help you to reason about the code by outputting these values, such as e.paramater.run to a spreadsheet, before line 2. Also before line 2, output the result of this[e.paramter.run] which is probably undefined. That way you can inspect and see what's going on.

I also kinda wonder if there is a reason why you use its own script as a web app when you could just execute script_showRows directly? Or maybe you're just providing minimal code to reproduce it?

Adam

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.