Building a Google Script Function for POST Requests: A Very Normal Guide

As many of you know, Google Sheets is my love language. One of the best features baked into this incredible software is Google Apps Script.

Sometimes we sheets-enthusiasts find ourselves will multiple separate spreadsheets that need to talk to one another. For this, Google has provided us with ImportRange and other such delights.

What if you made the terrible design decision to have one Google Spreadsheet with some App Script Code that a separate Google Spreadsheet needs to call? The easiest thing to do is to just copy and paste the Google Apps Script code from Spreadsheet A to B. This may not be feasible, however, if the script is highly contextualized in Spreadsheet A.

I found myself with this exact dilemma and the solution I landed on was to write a function in Spreadsheet B that sends a post request to the Google Apps Script on Spreadsheet A. When Spreadsheet A receives a request from Spreadsheet B, it has the ability to run any and all Apps Script already available to it.

More specifically, my Spreadsheet A is a database of running data. I use it to pull run activities from Strava on a daily timer because I am very normal. Spreadsheet B is where I plan my runs. I would like Spreadsheet B to know when a run is completed but to do so, Spreadsheet A has to request new data from Strava.

In this blog post, I will show you how to create a Google Apps Script function that accepts POST requests and returns a simple string response, but in addition to returning a string response you can have it call any function.

Creating the Google Script Function

To begin, we'll write a function called doPost(e) in Google Apps Script attached to Spreadsheet A. This function serves as the entry point for handling incoming POST requests. The function is defined as follows:

function doPost(e) {
  return ContentService.createTextOutput("Hello World.");
}

This script takes the incoming request and responds with the string "Hello World." It's a basic example, but you can modify it to perform more complex operations based on your requirements.

Deploying the Web App

To make the function accessible via a URL, we need to deploy it as a web app. Here are the steps to follow:

1. Open the Google Apps Script editor and create a new or existing script.
2. Replace any existing code with the doPost function.
3. Save the script and go to "Publish" > "Deploy as web app" in the menu.
4. Set the execution options to execute the app as your Google account and allow access to anyone, even anonymous users.
5. Click the "Deploy" button and note the URL provided for your web app.

One point that may be confusing here is that if you change any code in this new Web App, you'll have to re-deploy and again take note of the new URL provided.

Sending a POST Request

Now that the web app is deployed, we can send a POST request to it. In Spreadsheet B's Apps Script code editor we'll write:

function sendPostRequest() {
  var url = "https://script.google.com/macros/s/.../exec";
  var options = {
    method: "post"
  };
  
  var response = UrlFetchApp.fetch(url, options);
  var content = response.getContentText();
  
  Logger.log(content);
}

Here, we define the sendPostRequest function, which uses the UrlFetchApp service to send a POST request to the web app's URL. It logs the response content, which should be "Hello World."

Enhancing the POST Request

If you want to send data along with the POST request, you can modify the sendPostRequest function. For example, here's how you can send JSON data:

function sendPostRequestWithData() {
  var url = "https://script.google.com/macros/s/.../exec";
  
  var data = {
    name: "Very Normal",
    quantity: 123
  };
  
  var options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(data)
  };
  
  var response = UrlFetchApp.fetch(url, options);
  var content = response.getContentText();
  
  Logger.log(content);
}

In this example, the data object contains the payload data, which is then included in the POST request as JSON.

Because the web app is Spreadsheet A is accessible by anyone who knows the url, you can require some rudimentary authorization by checking for specific data in the post request.

Conclusion

You now have the foundation to build a Google Script function that handles POST requests. By following the steps outlined and modifying the code to fit your needs, you can create web apps that interact with incoming POST data. Go forth and prosper!