CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptweb-applications
Vineeth Varma
Vineeth Varma

Reputation: 609

How to call Google App Script from a web app?

Task: Open a google spreadsheet in web app, select a portion of values in that spreadsheet and on clicking a button on my web app, I should get selected values.

I tried to this through Google App script (following code). This works fine when I run the script from online having spreadsheet opened in another tab.

function doGet() 
  {
    var sheet = SpreadsheetApp.getActiveSheet();
    var data = sheet.getActiveRange().getValues();
    //Logger.log(data.length);
    for (var i = 0; i < data.length; i++) 
      {
         var output = HtmlService.createHtmlOutput("Product name: " + data[i][0] + ", Quantity:" + data[i][1]);  
      }
  
    //Logger.log(output.getContent());
    return HtmlService.createHtmlOutput(output);
  };

But when I try to deploy the script as web app and take the published url as the action for my form when I click button in my web app. Its not working.

<form action="https://script.google.com/macros/...." method="get">
  <input type="submit" value="call">
</form>

I added the above code in my webapp, where the spreadsheet is opened and displayed!

Now, when I click the "call" button from my web app it throws error

TypeError: Cannot call method "getActiveRange" of null.

How do I fix this?

Upvotes: 0

Views: 1506

Answers (2)

Wicket
Wicket

Reputation: 38160

As of August 7th, 2024, the code in question no longer throws the stated error, however, the code doesn't work as the web app returns

Product name: , Quantity:undefined

Note: My test spreadsheet has one blank sheet.

The above occurs because when using SpreadsheetApp.Sheet.getActiveRange(), nowadays, the returned range will be the cell A1 from the first sheet when calling this method from a web app and other similar contexts.

If the data to be read is on the first sheet, a quick fix to avoid getting undefined might be to use SpreadsheetApp.Sheet.getDataRange() instead.

It's worth noting that the for loop is rewriting the output variable.

Below is a proposal about making a simple fix to the question code:

function doGet(e) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getValues();
  const htmlOutput = HtmlService.createHtmlOutput();
  for(let i = 0; i < values.length; i++){
    htmlOutput.append("Product name: " + values[i][0] + ", Quantity:" + values[i][1] + "<br>")
  }
  return HtmlService.createHtmlOutput(htmlOutput);
}

Please note that this will work on an Apps Script project containing a spreadsheet with the required data on the first sheet when the web app is deployed as Executed as Me (the spreadsheet owner) or Executed as the user when the web app user has access to the spreadsheet.

Related

Upvotes: 0

Mogsdad
Mogsdad

Reputation: 45720

These lines have meaning when you're running the script within a spreadsheet:

    var sheet = SpreadsheetApp.getActiveSheet();
    var data = sheet.getActiveRange().getValues();

...but they don't when run from a web app. There's no "active sheet" or "active range" in that case.

You will need to express those as:

    var sheet = SpreadsheetApp.openById('--ss_id--').openSheetByName('--sheet-name--');
    var data = sheet.getRange(--Some-range-expression--).getValues();

... or a variation of that.

You cannot interact between a user in the spreadsheet and the web app, to pass the selected cells, for instance. You will need to re-think your task. For instance, you could select the cells in the sheet & make them a named range that is served via web app. Or you could add a menu item to create the named range.

Upvotes: 0

Related Questions