Turn your Google Sheet into a REST API and access it in any app.

Turn a Google Sheet into a REST API

Ravgeet Dhillon

Ravgeet Dhillon

Written on Mar 08, 2021 in Development

⏱ 5 min read

Blog banner for Turn a Google Sheet into a REST API

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

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

Format for Google Spreadsheet
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
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.

📫

Loved this post? Join our Newsletter.

We write about React, Vue, Flutter, Strapi, Python and Automation. We don't spam.

Please add a valid email.
By clicking submit button, you agree to our privacy policy.
Thanks for subscribing to our newsletter.
There was some problem while registering your newsletter subscription. Please try again after some time or notify the owners at info@ravsam.in

ABOUT AUTHOR

Ravgeet Dhillon

Ravgeet is a Co-Founder and Developer at RavSam. He helps startups, businesses, open-source organizations with Digital Product Development and Technical Content Writing. He is a fan of Jamstack and likes to work with React, Vue, Flutter, Strapi, Node, Laravel and Python. He loves to play outdoor sports and cycles every day.

Got a project or partnership in mind?

Let's Talk

Contact Us ->