CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-account
TPotter
TPotter

Reputation: 11

Google Script experienced an error only for other users (works fine for me)

I'm new to this and created a simple script to collect data with a save button. It works fine for me as the developer, but other users get an error message: "Script [name_of_script] experienced an error"

This is Google Apps Script is linked to simple Google Sheet, which is shared with several colleagues. They are to input some 10 data points, hit a 'save' button to save the data onto a 'data' tab (same Google sheets file) and clean the cells.

When I do hit the 'save' button it does as intended. When I tested it from another browser (not signed into my Google account), I get the above error message. The same applies to my colleagues (who also are not using a google account). I thought this is to do with the Deployment, and I've tried all types of Deployment profiles, variating "Execute as" and "who has access" to no avail.

The forms and the sheets are un-protected (although I would like to protect them if all goes well).

As for 'Authorization' - I'm not sure what that means.

Many thanks for your help.

function submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("Data"); //Data Sheet
   
  //Input Values
  var values = [[formSS.getRange("E26").getValue(),
      formSS.getRange("E5").getValue(),
      formSS.getRange("E7").getValue(),
      formSS.getRange("E9").getValue(),
      formSS.getRange("E11").getValue(),
      formSS.getRange("E13").getValue(),
      formSS.getRange("E15").getValue(),
      formSS.getRange("E18").getValue(),
      formSS.getRange("E20").getValue(),
      formSS.getRange("E22").getValue(),
      formSS.getRange("E24").getValue()]];
  datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 11).setValues(values);

 formSS.getRange('E5:E24').clearContent();
SpreadsheetApp.getUi().alert('Thanks')
}

Upvotes: 1

Views: 1244

Answers (1)

Wicket
Wicket

Reputation: 38435

You assigned a function from a spreadsheet bounded Google Apps Script to an image to be used as a button. The spreadsheet is shared with visitors (users not having a Google account). The visitors get an error message when they click the image. You tried different methods of deploying your script.

The script deployment is used for scripts to be used as Gmail, Google Calendar, Google Workspace Add-ons and Web Applications. A deployment hasn't an effect over functions called from container, in this case from the spreadsheet. Unfortunately there isn't a workaround that will make work calling a function by the spreadsheet visitors.

The most simple option to get data entry by "visitors" (users without a Google account) to be logged into a Google spreadsheet is to use a Google Form. Other "easy" options are to use Google Apps Script to create a web application and to use AppSheet.

Resources

Upvotes: 1

Related Questions