How to Convert Google Sheets Data to JSON Data API

This article explains how to convert Google Sheets Data to JSON Data API. You can convert Google Sheets data as a JSON API and use it as another front-end apps.

Google Sheets to JSON Data
Google Sheets to JSON
  • Step 1 – Create a new Google Sheets or open an existing one
  • Step 2 – Create data or table in any spreadsheet to be converted to JSON data
  • Step 3 – Open Script Editor – Tools>>Script Editor
  • Step 4 – Copy-paste the below code in the opened script file, change range and sheet name per your data

Google Script code to Convert Google Sheets Data to JSON Data:

function doGet() {

const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');//change sheet name per your need
const dataRange = ss.getRange("A1").getDataRegion().getValues();
const headers = dataRange.shift(); 

const jsonDataArray = dataRange.map(row =>{
  let object = {};
  headers.forEach((head, i) => {
    object[head] = row[i];
});
  return object;
});

const res = [{status : 200, data:jsonDataArray}];

return ContentService
          .createTextOutput(JSON.stringify(res))
          .setMimeType(ContentService.MimeType.JSON);
}

  • Step 5 – Click on Deploy (Blue button on the top right)
  • Step 6 – New Deployment>>WebApp
  • Step 7 – Enter Description>>Select who has access>>Deploy
  • Step 8 – Copy the URL>>Done
  • Step 9 – Paste the URL in the browser and Test the App
  • Now you are good to go and can use it as backend JSON data API.

    Read More:

    COMMENTS

    Open chat
    Talentnett Solutions
    Hello,

    How may I help you today!

    Regards,
    Umesh Agarwal
    Contact - +91 - 7017544508
    Email - umesh.agarwal@talentnett.com

    Cool Tips and Tricks Directly Delivered in your Inbox. Subscribe to our Newsletter.

    You have successfully subscribed to the newsletter

    There was an error while trying to send your request. Please try again.

    Talentnett will use the information you provide on this form to be in touch with you and to provide updates and marketing.