Unleashing the Power of Spreadsheets: Building a REST API with Google Sheet & Google Apps Script

Shaykh Farhan — Fri, 15 Mar 2024 10:50:15 +0000

Introduction
Prerequisites
Understanding REST APIs
Building the Google Sheet
Setting Up Google Apps
Creating the API with Google Apps Script
- doGet Function for Retrieving Data (GET Request)
- doPost Function for Retrieving Data (GET Request)
- Handling Multiple Routes
Limitations and Best Practices
Conclusion

Introduction:

In today's digital landscape, the demand for efficient data management and collaboration has never been greater. Google Sheets, a key component of Google's productivity suite, is a comprehensive tool for organizing, analyzing, and sharing data in real time. What if we could push the limits of its capabilities even further?

Enter the idea of leveraging Google Sheets to build RESTful APIs – a solution that combines the familiarity and accessibility of Google Sheets with the functionality of a traditional database-backed API. This tutorial aims to showcase precisely that – harnessing the power of Google Sheets as a provisional database while simultaneously serving RESTful APIs.

Traditional web development frequently requires setting up a backend server, configuring a database, and creating a frontend interface, all of which are time-consuming and expensive processes. In contrast, Google Sheets provides a simpler option. It functions as both a dynamic database and a real-time collaborative interface, allowing users to change content smoothly, similar to a content management system (CMS), but without the burden of server maintenance and database management.

Though this strategy has limits, it is an appealing choice for individuals, small enterprises, or anybody looking to develop web apps quickly and affordably. Throughout this article, we'll look at how to maximize the potential of Google Sheets as a backend solution, demonstrating its adaptability and practical uses in web development.

Let's get started and learn how to use spreadsheets to revolutionize the way you design online applications.

Prerequisites:

A Google account with access to Google Sheets and Google Apps Script.
Basic understanding of spreadsheets and working knowledge of web concepts like APIs.

Understanding REST APIs:

REST (Representational State Transfer) APIs are a popular architectural style for web APIs. They provide a standardized way for applications to communicate and exchange data. REST APIs typically utilize HTTP requests (GET, POST, PUT, DELETE) to interact with resources (data).

Building the Google Sheet:

Go to Google Sheet.
Create a new Google Sheet.
Design your data structure in the sheet. Each row can represent a data record, and columns represent specific attributes.
Ensure the sheet headers clearly define the data fields and give the sheet a name.

Setting Up Google Apps:

Open your Google Sheet.
Go to Extensions > Apps Script.
A new script window will open. This is where you'll write the code for your API.

Creating the API with Google Apps Script:

We'll focus on two essential functionalities:

Retrieving data from the sheet (GET request): This allows external applications to access data stored in the sheet.
Adding new data to the sheet (POST request): This enables external applications to submit new data entries to the sheet.

Apps Script gives two function doGet and doPost for handling GET and POST requests.

doGet Function for Retrieving Data (GET Request):

First, we need a function that can convert our data into JSON. Here is an example function that can convert data into JSON.

function convertToJson(data) {
  const jsonArray = [];

  // Check if data is empty or doesn't contain enough rows for headers and at least one data row
  if (!data || data.length < 2) {
    // Return an empty array or a meaningful message as needed
    return jsonArray; // or return 'No data available';
  }

  const headers = data[0];
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const record = {};

    // looping through each row
    for (let j = 0; j < row.length; j++) {
      record[headers[j]] = row[j];
    }

    jsonArray.push(record);
  }

  return jsonArray;
}

Now let's create a function that can return all user from user sheet as JSON.


function getAllUsers() {

  // selecting usersheet by name
  const userSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("user");

  // here i have data from 1st row and column you can customize your range.
  const data = userSheet.getDataRange().getValues();

  const jsonData = convertToJson(data);

  return jsonData;
}

Now let's write our doGet function

function doGet(e) {

  // our initial data structure
  const jsonData = {
    status: 200,
  };

  // getting all users
  const users = getAllUsers();

  // setting users into jsonData.data
  jsonData.data = users;

  // Convert data to JSON string
  const outputData = JSON.stringify(jsonData);

  // Create ContentService object with JSON type
  const content = ContentService
    .createTextOutput(outputData)
    .setMimeType(ContentService.MimeType.JSON);

  return content;
}

Save and rename the project. Final code should look like this.

Deploying the project:

Go to Deploy > New deployment
Select type Web app
Fill the details
Change Who have access to anyone
Click Deploy (It may ask you for authorization).
You will get your url.

Let's try the url on postman. You will see similar to this one.

doPost Function for Retrieving Data (GET Request):

We have handled GET request, now it's time for POST request and mutating the data.

Let's write a doPost function for handling POST request.

function doPost(e) {
  // Get the request payload (data)
  const data = JSON.parse(e.postData.contents);

  // some basic validation
  if (!data.email || !data.name || !data.username) {
    const outputData = JSON.stringify({
      status: 400,
      message: "All fields are required"
    });

    return ContentService
      .createTextOutput(outputData)
      .setMimeType(ContentService.MimeType.JSON);
  }

  // structure the req data as the requirements
  const structuredData = [data.name, data.username, data.email]

  // Access the sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("user");

  // Append the new data to the sheet (replace with your logic)
  sheet.appendRow(structuredData);

  const outputData = JSON.stringify({
    status: 200,
    message: "Data added successfully"
  })

  // Return a success message
  return ContentService
    .createTextOutput(outputData)
    .setMimeType(ContentService.MimeType.JSON);
}

Save and redeploy the app.
Now you can mutate the data of the sheet.

Handling Multiple Routes:

Due to the limitation, Apps Script doesn't give any features to handle multiple endpoints, but we can leverage the use of QueryParams and switch Statement.

Let's create an endpoint for users. Updated doGet function will look like this.

function doGet(e) {

  // getting path from query
  const path = e.parameter.path;

  // our initial data structure
  const jsonData = {
    status: 200,
  };

  switch (path) {
    case "users":
      // getting all users
      const users = getAllUsers();

      // setting users into jsonData.data
      jsonData.data = users;
      break;

    default:
      jsonData.status = 404;
      jsonData.message = "Invalid route";
  }

  // Convert data to JSON string
  const outputData = JSON.stringify(jsonData);

  // Create ContentService object with JSON type
  const content = ContentService
    .createTextOutput(outputData)
    .setMimeType(ContentService.MimeType.JSON);

  return content;
}

Example Route:
https://script.googleusercontent.com/macros/s/<script_id>/exec?path=users

In this example, path=users specifies the route (/users) you want to access.

Here is a screenshot for invalid routes.

Now you can also do it for the doPost function.

Limitations and Best Practices:

Building a REST API with Google Sheets and Apps Script is a potent solution, but it's important to understand its limitations.

Data Volume: Google Apps Script may be limited in its ability to efficiently analyze huge datasets.
Complex Logic: Creating intricate data manipulation logic can be more difficult than using a specialist web development platform.
Security: Proper authentication and permission systems are critical for protecting data from unwanted access.

Best Practices:

Suitable for Small to Medium Datasets: Perfect for handling smaller datasets and simple data interactions.
Consider alternatives for large-scale projects: Consider using known web development frameworks for large data handling or complicated capabilities.
Prioritize Security: Implement robust authentication and authorization measures to protect your data.

Conclusion

Simple data management: Streamline data sharing between your spreadsheet and other tools.
Personal projects or small-scale applications: Develop customized solutions without substantial coding experience.
Learning and experimentation: Gain valuable experience with building APIs and working with Google Apps Script.

I hope you've got a good idea on Google Sheet and Apps Script.
Remember, while this method offers a low-code solution, it's essential to be mindful of its limitations and prioritize data security. For complex scenarios or large datasets, consider exploring more robust development environments.

DEV Community: Shaykh Farhan