How to Create a Google Apps Script to Output Content from Google Sheets as JSON Data Using doGet()

by

Google Apps Script is a powerful tool that allows you to automate tasks across Google’s suite of apps. One practical use case is to create an API endpoint that outputs data from a Google Sheet as JSON. This can be particularly useful for integrating Google Sheets data with other applications. In this blog post, I’ll guide you through the steps to create a Google Apps Script to achieve this.

Step 1: Setting Up Your Google Sheet

  1. Create a new Google Sheet and populate it with some data. For this example, let’s assume you have a sheet named Sheet1 with the following columns: ID, Name, Email.
  2. Ensure the first row contains headers as these will be used as keys in the JSON output.

Step 2: Creating the Google Apps Script

  1. Open the Script Editor: In your Google Sheet, go to Extensions > Apps Script.
  2. Delete any existing code in the script editor and replace it with the following script:
function doGet(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var jsonArray = [];

  for (var i = 1; i < data.length; i++) {
    var rowObject = {};
    for (var j = 0; j < headers.length; j++) {
      rowObject[headers[j]] = data[i][j];
    }
    jsonArray.push(rowObject);
  }

  var jsonOutput = JSON.stringify(jsonArray);

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

Step 3: Deploying the Script as a Web App

  1. Save your script: Click on the floppy disk icon or go to File > Save, and name your project.
  2. Deploy as a web app:
    • Click on Deploy > New deployment.
    • In the Select type dropdown, choose Web app.
    • Give your deployment a description (optional).
    • Under Execute as, choose Me.
    • Under Who has access, choose Anyone.
  3. Authorize the script: You will be prompted to authorize the script to access your Google Sheets data. Follow the prompts to grant the necessary permissions.
  4. Get the web app URL: Once deployed, you will receive a URL for your web app. This URL is your new API endpoint.

Step 4: Testing the JSON Endpoint

  1. Open a new browser tab and paste the web app URL. You should see the data from your Google Sheet outputted as JSON.
  2. Use tools like Postman or cURL to test the endpoint further. For example, you can use the following cURL command in your terminal:bashCopy codecurl -L "YOUR_WEB_APP_URL"

Conclusion

Congratulations! You’ve successfully created a Google Apps Script that outputs content from Google Sheets as JSON data using the doGet() method. This simple API can now be used to integrate your Google Sheets data with other applications, allowing for seamless data sharing and automation.

Feel free to expand on this script by adding more functionality, such as filtering data based on query parameters, adding authentication, or writing data back to the sheet. The possibilities with Google Apps Script are endless!

Author’s Note: If you found this tutorial helpful, check out my books “Coding with Google Apps Script: A Step-by-Step Journey” and “Google Apps Script Sheets Custom Functions: Over 150 Apps Script Code Examples for Sheets” for more in-depth projects and examples.