Build a Logger API in Google Sheets / Google Apps Script

Do you ever just want to log something in an app you’re building? Like someone fills out a form, or you get an SMS, or whatever? But every time, you have to build a custom logger API?

This is a problem I often encounter. Usually I’m building an app, and I want to log instances where

• A user signs in or out
• A new user signs up
• There’s an error of some kind
• Someone buys something

For these instances, I need an API-based logger. But there’s nothing freely available out there, so I have to make my own, using Google Sheets and Google Apps Script.

And now I’m sharing it. You can deploy this in 10 minutes or less.

If you’re interested in understanding more about APIs, here’s an explainer in simple terms.

Updated in 2023, since Google Apps Script has updated a few times since I first published this in 2018.

Overview — How does this Logger API app work?

In essence, this logger app is a very simple application using Google Sheets and Google Apps Script as an API and database.

The simplification is that the logger is an app that just receives data. You don’t call the API to see what’s in the log; you do that by visiting the sheet.

However, you could definitely make a more complex logger app that lets you request log items

Step 1: Copy and configure the app

Start by opening this sheet.

Make a copy of it for yourself (as you can’t edit mine!).

Once you’ve made your own copy, copy the ‘id’ of the URL. You’re going to use this in the Google Apps Script code editor.

Now that you’ve copied that code, open Apps Script from the Extensions menu.

This will open the Google Apps Script editor window.

Now, in the code, update the code for ‘id’ with the id of your workbook. Here’s the section of code you have to update:

<code>// id for your Google Sheet. You need to update this!
var id = 'YOURSHEETCODE';

Your code is now finished, and you’re ready to publish the app and start using it.

Step 2: Publish the app

You need to deploy the app, and make sure anyone can access it. (It’s a little complicated to configure data restrictions and security in Google Apps Script — that’s one of the simplifications I’m making to make this app easy to get off the ground.)

First, click “Deploy” and “New deployment”.

Next, configure it so that the app will execute as yourself (so it can update the sheet you own), but that anyone can access it.

You’ll probably have to authorise the app to work.

Finally, your app is deployed. You now have an URL to use for your Google Apps Script-based data logger.

Copy that URL. You’re going to use it to test the app.

Step 3: Use the app

It’s now ready to use as a logger from your project (in Python, JS or whatever).

To test the logger and make sure it works, we’re going to use a curl command from the terminal on a Mac or Linux server.

curl -L -X POST -d "log=This is not a test" https://script.google.com/macros/s/YOURAPPCODE/exec

Data will end up magically in the Google Sheet. Hooray!

Side notes

You won’t receive a clean 200 response, but probably a redirect, or a “Sorry, this page is inaccessible” error. The logger still works, though.

The Code

If you’re interested in examining the code, it’s below.

// id for your google sheet. You need to update this!
var id = 'YOURSHEETCODE'; 

function doGet(e) {
  return HtmlService.createHtmlOutput("OK");
}

function doPost(e) {
  var logText = e.parameters.log[0]; // Access the first item of the array
  
  // log line
  logLine(logText);
  
  //return something
  return ContentService.createTextOutput("Success! Added "+logText+" to log.").setMimeType(ContentService.MimeType.TEXT);
}


function logLine(text) {
  // get the sheet
  var sheet = SpreadsheetApp.openById(id).getSheets()[0];
  
  // figure out date right now
  var nowDate = Utilities.formatDate(new Date(), "GMT-7", "dd/MM/yyyy HH:mm:ss");
  
  // find last row
  var lastRow = sheet.getLastRow() + 1;
  
  // update date
  sheet.getRange('A'+lastRow).setValue(nowDate);
  
  // log line
  sheet.getRange('B'+lastRow).setValue(text);
}