HTTP Server on Google Sheets in GAS

Overview

This post describes how to build an HTTP server on Google Sheet using Google Apps Script (GAS).

HTTP GET

1. Google Sheets > Extensions > Apps Script

2. Add doGet(e) function, like:

function doGet(e) {
  var str = 'Hello, ' + e.parameter.name;
  var resp = {greeting: str};
  return ContentService.createTextOutput(JSON.stringify(resp)).setMimeType(ContentService.MimeType.JSON);
}

3. Deploy > New deployment

4. Select type > Web app > Access = Anyone > Deploy

5. Test

% curl -L https://script.google.com/macros/s/xxxxxxx/exec?name=World
{"greeting":"Hello, World"}

"-L" option is needed to follow the redirected page, otherwise the result will show "Moved Temporarily". 

HTTP POST

Just like HTTP GET, add doPost(e) function, then deploy the project.

The following example takes the name parameter and write it on the sheet.

function doPost(e) {
  var name = e.parameter.name;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  sheet.getRange('A1').setValue(name);
  var resp = {status: 'done'};
  return ContentService.createTextOutput(JSON.stringify(resp)).setMimeType(ContentService.MimeType.JSON);
}

Authorize the access as needed.

Test

% curl -L -d'name=World' https://script.google.com/macros/s/yyyyyy/exec
{"status":"done"}