Simple JSON API with Google Sheets

Google Sheets is a versatile tool, often used for spreadsheets and lightweight data storage. But did you know it can also act as a simple database for a JSON API? With Google Apps Script, you can turn your spreadsheet into a backend service that accepts POST requests to insert data and GET requests to retrieve it. Let’s dive in.

The Setup

First, you need a Google Sheet. In this example, we’re using a sheet named Sheet1. The first row contains headers (like Name, Email, Date, etc.), and subsequent rows will store data submitted via the API.

We'll use Google Apps Script to handle the API requests:

const sheetName = 'Sheet1';
const scriptProp = PropertiesService.getScriptProperties();

Here, we define the sheet we’re using and a script property storage. PropertiesService is handy for storing the Spreadsheet ID so our functions can reference it reliably.

Initial Setup Function

Before we can handle requests, we need to store the active spreadsheet’s ID:

function initialSetup() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  scriptProp.setProperty('key', activeSpreadsheet.getId());
}

Run this function once to initialize the script. It saves your spreadsheet’s ID in the script properties so your API can open it later.

Handling POST Requests

To insert data into the spreadsheet via an API request, we use the doPost function:

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    const sheet = doc.getSheetByName(sheetName);

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const nextRow = sheet.getLastRow() + 1;

    const newRow = headers.map(function (header) {
      return header === 'Date' ? new Date() : e.parameter[header];
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    return ContentService.createTextOutput(
      JSON.stringify({ result: 'success', row: nextRow })
    ).setMimeType(ContentService.MimeType.JSON);

  } catch (e) {
    return ContentService.createTextOutput(
      JSON.stringify({ result: 'error', error: e })
    ).setMimeType(ContentService.MimeType.JSON);

  } finally {
    lock.releaseLock();
  }
}

Handling GET Requests

To retrieve all the data in JSON format, we use the doGet function:

function doGet() {
  const doc = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = doc.getSheetByName(sheetName);
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getDisplayValues();

  const result = values.map((col) => {
    let obj = {};
    headers.forEach((header, index) => {
      obj[header] = col[index];
    });
    return obj;
  });

  return ContentService.createTextOutput(
    JSON.stringify({ data: result })
  ).setMimeType(ContentService.MimeType.JSON);
}

Putting It All Together

After writing the script:

1. Save the script in your Google Sheets via Extensions → Apps Script.
2. Run initialSetup() once.
3. Deploy it as a web app (Deploy → New deployment → Web app).
4. Set access to Anyone with the link if you want it public.

Now, you have a fully functional API backed by Google Sheets:

• POST to insert data.
• GET to retrieve data.

Advantages

• No external database needed: Uses Google Sheets.
• Simple JSON API: Perfect for small projects, prototyping, or internal tools.
• Automatic timestamp: Records the date of submission.

Limitations

• Performance: Suitable for low-traffic scenarios. Google Sheets has row limits.
• Security: If public, anyone can post data. Consider adding authentication.
• Concurrency: Locking helps, but heavy traffic may still cause conflicts.

Conclusion

With just a few lines of code, you’ve turned Google Sheets into a simple JSON API that can receive and send data. This is perfect for prototyping, small projects, or internal tools.

You now have a fully functioning backend without setting up a server—Google Apps Script and Sheets handle it all!