How to host Google sheet in JSON format using Wix Code

by guest author Salman

This article will show you how to host any google sheet in JSON format.

To do this trick we must need two things host the google sheet in web and get the sheet id

1. Host the google sheet in the web

a. On your google sheet click on the File menu on the top left, then click on Publish to the web...

b. Window will pop up as shown in the below image, Click on Publish and OK (in the alert box)

 Step one is completed, step two is more simple get the google sheet id

2. Get the sheet ID

You can get the sheet id from the URL

URL format

https://docs.google.com/spreadsheets/d/{your google Sheet ID}/edit#gid=0

Example:-

my google sheet URL is

https://docs.google.com/spreadsheets/d/1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg/edit#gid=0

So, the sheet id is 1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg

That's it.

Now the fun part.

After you publish, Google will host the sheet in the below URL format

https://spreadsheets.google.com/feeds/list/{sheetId}/{sheetNumber}/public/values?alt=json

Replace the sheet id and sheet number

Default sheet number is 1 (which means the 1st sheet)

So, the public URL will be as follows

https://spreadsheets.google.com/feeds/list/1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg/1/public/values?alt=json

if you go to the above URL, it will show a bunch of text in JSON format

but, it has too much information we don't need.

so, we need a function to get only the sheet data

 // main code
 async function getSheet(sheetId, sheetNumber = 1) {
  let url = `https://spreadsheets.google.com/feeds/list/${sheetId}/${sheetNumber}/public/values?alt=json`;
 
  let res = await fetch(url);
  if (!res.ok) throw "status is not ok";
  let data = await res.json();
  let json = [];
  data.feed.entry.forEach(el => {
  let row = {};
  Object.keys(el).forEach(col => {
  if (col.slice(0, 4) === "gsx$") {
  let title = col.slice(4);
  row[title] = el[col].$t;
  }
  });
  json.push(row);
  });
  return json;
 }
 
 getSheet("1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg")
 .then(sheet =>{
  console.log(sheet);
 })
 

run this code on your code editor or console.

commented version of the code will be on the below

if you want that you can use it.

now let's break down the code.

Used async/await for better readability, using await we can resolve the Promise and get the Fulfillment value in a single line instead of using .then() another main advantage is it won't run the next line until the promise is either resolved or rejected.
 
 Used Fetch you can learn more about it in
 https://github.github.io/fetch/
 
 to use fetch in Wix site
 you will need to add this one line on top of the code

 import {fetch} from 'wix-fetch';
 

documentation of wix-fetch module
 https://www.wix.com/corvid/reference/wix-fetch.html#fetch

 // code explnation
 async function getSheet(sheetId, sheetNumber = 1) {
  // public URL format of google sheet will take
  // sheet ID and sheetNumber from the function parameter
  // default sheetNumber is set to 1
  // If you want to get the different sheet you will need to pass
  // on the function call
  // getSheet("someSheetId" , 2);
  // this will fetch for the second sheet
  let url = `https://spreadsheets.google.com/feeds/list/${sheetId}/${sheetNumber}/public/values?alt=json`;
 
  // geting the url and resolving the promosie using await
  // the response(res) will be stored in the "res" variable
 
  let res = await fetch(url);
  // checking if the url is ok (code 200)
  // if it's not okay it will stop the function
  // throw the error with the message "status is not ok"
  // which we capture it using .catch function
  if (!res.ok) throw "status is not ok";
 
  // if the status is okay
  // we are going to request the data using .json()
  // which is a promise, so we need to use await keyword
  // to get the result in single line
  let data = await res.json();
  // creating an empty json array to store the filtered value
  let json = [];
  // the value of the sheet will be in entry
  // which is an array
  // so using forEach method to loop through each value
  data.feed.entry.forEach(el => {
  // creating an empty row for each loop
  let row = {};
  // Using Object.keys getting all the keys in array
  // this array will have the title of the sheet (1st row)
  // ["gsx$firstname" , "gsx$lastname", "gsx$email"...
  // gsx$ represent that it's the title
  // so, we are only gonna check if the first four letter
  // matchs the gsx$ using .slice(0, 4)
  Object.keys(el).forEach(col => {
  // checking if the current title start with gsx$
  if (col.slice(0, 4) === "gsx$") {
  // removing the gsx$ and storing the value
  // in title variable
  let title = col.slice(4);
  // creating key and value on the row object
  // assigning the key as the title variable
  // and the correstpoing value
  // which is under .$t
  row[title] = el[col].$t;
  }
  });
  // after each row object created will be pushed to the
  // json array
  // row = {
  // companyname: "Kaymbo"
  // email: "vali12dew@ca.gov"
  // firstname: "Vali"
  // gender: "Male"
  // lastname: "Dewerson"
  // }
  json.push(row);
  });
  return json;// end of function
 }
 
 // calling getSheet function with the sheet ID
 getSheet("1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg")
 .then(sheet =>{
  // after the promise is resolved getting the sheet
  console.log(sheet);
 });
 

created a live working sample: https://salman2301.wixsite.com/google-sheet using wix site

Live demo page in iFrame

Wix code used on above demo page

 import { fetch } from 'wix-fetch';
 
 $w.onReady(function () {
  $w('#btnFetch').onClick(async () => {
  // getting the sheet id from the input variable
  let sheetId = $w('#inSheet').value;
  getSheet(sheetId)
  .then(json => {
  console.log("JSON : ", json);
  $w('#textBoxResult').value = JSON.stringify(json, null, 4);
  })
  .catch(err => {
  $w('#textBoxResult').value = err.message + "\n 1. Check if the google sheet is published properly. \n 2. Sheet may not have the permission, click on \"Share\" set as \"anyone can view\"";
  });
  });
 
 });
 
 async function getSheet(sheetId, sheetNumber = 1) {
  let url = `https://spreadsheets.google.com/feeds/list/${sheetId}/${sheetNumber}/public/values?alt=json`;
 
  let res = await fetch(url);
  if (!res.ok) throw "status is not ok";
  let data = await res.json();
  let json = [];
  data.feed.entry.forEach(el => {
  let row = {};
  Object.keys(el).forEach(col => {
  if (col.slice(0, 4) === "gsx$") {
  let title = col.slice(4);
  row[title] = el[col].$t;
  }
  });
  json.push(row);
  });
  return json;
 }
 

that's it, thanks for reading.

If you have a question, please see contact details below.

Author

Stuck on a project? Hire Salman!

Email: admin@salman2301.com

Site: https://www.salman2301.com/

Facebook Message click here.