Wouldn’t it be awesome if you can use your Google Sheets as a CMS? What if you want the data in your Google Sheet to be publicly available? This can be done easily using Google Sheets and Google Apps Script.

In this blog, you’ll take a look at how you can convert a Google Sheet into a REST API and access it publicly from any app you want.

Contents

• Setting up a Spreadsheet
• Creating a Google Apps Script
• Converting Data to JSON format
• Creating a Web App
• Results

Setting up a Spreadsheet

The first task is to set up a Spreadsheet and initialize it with some data.

Google Spreadsheet with some data

Creating a Google Apps Script

The next step in your journey is to be able to access the data in the Google Sheet. So, from Tools, select Script Editor. This will create a new Apps Script project.

In your newly created Apps Script project, create a Code.gs file and add the following code to it:

function json(sheetName) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = spreadsheet.getSheetByName(sheetName)
  const data = sheet.getDataRange().getValues()
  const jsonData = convertToJson(data)
  return ContentService
        .createTextOutput(JSON.stringify(jsonData))
        .setMimeType(ContentService.MimeType.JSON)
}

In the above function:

1. You get the current active spreadsheet to which this Apps Script project is linked with.
2. You get your specific sheet by its name.
3. You retrieve the data in that sheet.
4. You convert the data to JSON format using convertToJson function and store it in jsonData variable.
5. Finally, you return the JSON response.

Converting Data to JSON format

The data returned by the sheet.getDataRange().getValues() is of the following format:

[
  ['name', 'age', 'role'],
  ['John', 28.0, 'Front End Engineer'],
  ['Marry', 21.0, 'Staff Engineer'],
  ['Jackson', 22.0, 'Backend Engineer']
]

In the above snippet, you can see that there is a custom function convertToJson that needs to be written. To convert your sheet data with headers into JSON format, add the following code in Code.gs file:

function convertToJson(data) {
  const headers = data[0]
  const raw_data = data.slice(1,)
  let json = []
  raw_data.forEach(d => {
      let object = {}
      for (let i = 0; i < headers.length; i++) {
        object[headers[i]] = d[i]
      }
      json.push(object)
  });
  return json
}

Creating a Web App

To access your Google Sheet as a REST API, you need to deploy your Google Apps Script as a Web App. This web app will handle the GET requests.

In the Code.gs file, add the following code:

function doGet(e) {
  const path = e.parameter.path
  return json(path)
}

Once you are done with this, the final step is to publish your Apps Script as a Web App. You can simply create a New Deployment and set the Execute As to me and Who has access to Anyone. These settings allow your Web App to be publicly accessible.

Results

To the webapp, you can send a GET request to it by using Postman. The path for the GET request would be your Web App’s URL and query parameter path would be your Google Sheet’s name.

In this case, the URL is https://script.google.com/macros/s/AKfycbw9gpHbIauF8obidyDjxe3_L9qA-Ww-e8bv6pvNNGavAv-xxxxxxxxxxxxxxxxxxxxxxx/exec?path=people.

Alright! You can see that you have transformed your Google Sheet into a REST API in under five minutes using the above code.

Google Sheet data returned in a REST API format

You can add more sheets in your spreadsheet and access them simply using the sheet name in the path query parameter when sending a GET request.