Search code examples
google-apps-scriptgoogle-sheetsweb-applicationsluahttprequest

HTTP Request to a function in Google Scripts

Since I'm not experienced at all with HTTP Request and Google Scripts, I'm having trouble wraping my head around it.

So, my problem is the following:

I'm currently trying to get information in my lua script and send it to a google Spreadsheet. However, the way the google spreadsheet should save the info would be dependent on which function on the Google Script I'm calling and passing information.

SO, my question is: How would my lua script (that only gives me access to HTTP Requests at this time) connect to a specific function like the one bellow?

function callName(name) {

  // Get the last Row and add the name provided
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1,1).setValue([name]);
}

Also, I think my script is wrong as well, but I'm more worried about how to actually make the connection.

Solution

  • Answer:

    You can publish your script as a Web Application and use URL parameters to pass the script the information you need.

    More Information:

    From the Google documentation about web apps:

    If you build a user interface for a script, you can publish the script as a web app. For example, a script that lets users schedule appointments with members of a support team would best be presented as a web app so that users can access it directly from their browsers.

    However, even without building a user interface, you can use this functionality to run scripts on your sheet by utilising HTTP requests.

    Modifying your Script:

    In order to allow your script to accept URL parameters, you must first modify your code so that processing is done on a HTTP GET request. You can do this with the Apps Script doGet() function and the event parameter e:

    function doGet(e) {
  callName(e.parameter.name);
}

function callName(name) {
  // Get the last Row and add the name provided
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1,1).setValue([name]);
}

    Setting up the Web App:

    From the Apps Script user interface, follow the Publish > Deploy as web app... menu item, and in the newly-opened modal window, you'll want to select the following settings:

    • Project version: New
    • Execute the app as: Me (your-email@address.here)
    • Who has access to the app: Anyone, even anonymous

    And click Deploy. Here, you will be given a URL in a new, smaller modal in the following form:

    https://script.google.com/a/your-domain.com/macros/s/some-script-id/exec

    Making the request:

    The rest of this is now trivial - you can make your HTTP request to the script URL in the previous step, but providing the URL parameter that you need in order to give te app the information of the value you wish to set.

    For example, if you want to set the value to the number 20, make your get request as so:

    GET https://script.google.com/a/your-domain.com/macros/s/some-script-id/exec?name=20

    Note the ?name=20 at the end gives the Web App the parameter name with a value of 20. The doGet(e) function reads this from e.parameter.name and sends it to your callName(name) function for processing and execution.

    References: