How to Use Google Sheets Apps Script to Build a Bidirectional REST API Using doGet and doPost

Google Sheets is primarily designed as a collaborative spreadsheet tool, but its underlying Apps Script engine allows it to function as a powerful, lightweight backend database. By leveraging the built-in doGet and doPost event handlers, developers can transform any Google Sheet into a fully functional, bidirectional REST API. This approach is ideal for prototyping applications, building simple webhook receivers, or integrating third-party services without the overhead of deploying a traditional database server.

Understanding the Apps Script Web App Architecture

When you deploy a Google Apps Script project as a Web App, Google assigns it a unique URL. HTTP requests sent to this URL trigger specific functions within your script. Specifically, HTTP GET requests trigger the doGet(e) function, while HTTP POST requests trigger the doPost(e) function. The e parameter represents the event object, which contains all the query parameters, header information, and payload data passed in the HTTP request.

To begin, open your Google Sheet, navigate to Extensions, and select Apps Script. This opens the script editor bound directly to your spreadsheet.

Implementing the GET Endpoint for Data Retrieval

The doGet function is used to retrieve data from the spreadsheet and return it to the client, typically formatted as JSON. This allows external applications to read the contents of the Sheet.

function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  var result = [];
  var headers = data[0];
  
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var obj = {};
    for (var j = 0; j < headers.length; j++) {
      obj[headers[j]] = row[j];
    }
    result.push(obj);
  }
  
  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

This script extracts all data from the active sheet, assumes the first row contains column headers, and maps each subsequent row into a JSON object. The ContentService module ensures the response is correctly formatted with the application/json MIME type, mimicking a standard REST API response.

Implementing the POST Endpoint for Data Ingestion

To write data to the spreadsheet, you must implement the doPost function. This function intercepts incoming POST payloads, parses the JSON, and appends the data as a new row in the Sheet.

function doPost(e) {
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var payload = JSON.parse(e.postData.contents);
    
    // Assuming the JSON payload has keys that match your headers
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var newRow = [];
    
    for (var i = 0; i < headers.length; i++) {
      var headerName = headers[i];
      newRow.push(payload[headerName] || "");
    }
    
    sheet.appendRow(newRow);
    
    return ContentService.createTextOutput(JSON.stringify({"status": "success", "message": "Row added successfully"}))
      .setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({"status": "error", "message": error.toString()}))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

This script parses the incoming JSON string, matches the JSON keys against the spreadsheet headers, constructs a new array, and uses appendRow() to insert the data. It also includes basic error handling to return meaningful API responses if the parsing fails.

Deploying and Securing Your API

To make the API accessible, click Deploy and select New deployment. Choose Web app as the deployment type. Crucially, under the security settings, you must decide who has access. If you are integrating with an external service (like a webhook from Stripe or GitHub), you typically must set the access level to Anyone. If set to Anyone, the script runs under your Google account permissions, meaning the external service does not need to authenticate with Google; it simply hits the URL.

Because the endpoint is publicly accessible, you should implement an application-level security token. You can require the client to pass a specific token in the query parameters or the POST payload, and wrap your doGet and doPost logic in a simple validation check to reject unauthorised requests.