Troubleshooting Google Sheets Script: Unexpected Error in Student Grade Display

Other Questions Google Sheets
1

I’m running into a weird problem with my Google Sheets script for my class. It’s supposed to show students their grades when they enter a password. The script works fine when I use Browser.msgBox() to display the info, but it throws an unhelpful error when I try to show it in the app UI.

Here’s what the script does:

  1. Opens a spreadsheet with student data
  2. Creates a simple UI with a password box
  3. Checks the password and finds the matching student
  4. Pulls the student’s grades from the sheet
  5. Formats the grades into a string
  6. Tries to display the grades in a label

The error I get is just ‘Error encountered: An unexpected error occurred.’ Not very helpful!

Here’s a simplified version of the problematic part:

function showGrades(password) {
  var app = UiApp.createApplication();
  var sheet = SpreadsheetApp.openById('some_id');
  var studentData = sheet.getRange('A1:Z50').getValues();
  
  var grades = findStudentGrades(studentData, password);
  var formattedGrades = formatGrades(grades);
  
  var label = app.createLabel(formattedGrades);
  app.add(label);
  
  return app;
}

Any ideas why it works with Browser.msgBox() but not when I try to display it in the app? I’m stumped!

2

I’ve dealt with similar headaches when working on Sheets scripts. One thing that might be causing the issue is the way you’re handling the UiApp. Google’s been phasing that out for a while now.

Have you considered switching to HTML Service instead? It’s a bit more work upfront, but it’s way more flexible and less prone to weird errors. You’d basically create an HTML file for your UI, then use google.script.run to call your server-side functions. Something like:

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');
}

function getGrades(password) {
  // Your existing logic here
  return JSON.stringify(formattedGrades);
}

Then in your HTML, you’d have a form and use JavaScript to display the results. It’s a bit of a learning curve, but it’s saved me tons of headaches in the long run. Plus, it’s what Google recommends now for custom UIs in Sheets.

3

hey swiftcoder15, sounds like a tricky issue! have u tried logging the formattedGrades variable to see if its actually getting the data? might be worth checking if theres any funky characters or formatting that the UI doesn’t like. also, double-check ur permissions - sometimes sheets can be picky about what scripts can access. good luck troubleshooting!

4

I encountered a similar issue when working with Google Sheets scripts. The problem might be related to how you are handling the UiApp; note that UiApp is deprecated and Google now recommends the use of HTML Service.

Instead of using the old approach, try refactoring your code to employ HtmlService. For example, create an HTML file for your interface, call your server-side functions using google.script.run, return the grades as JSON from your server-side function, and then update the HTML accordingly. This method is more robust and should help avoid the unexpected error you encountered. Let me know if you need further details.