How to host Google sheet in JSON format using Wix Code

by guest author Salman

Details


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


Setup

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.

Main Code


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.


Break down of Main 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);
});



Live Working


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

by Salman




Stuck on a project? Hire Salman!

Email: admin@salman2301.com

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

Facebook Message click here.




36 views0 comments

disclaimer

a quick note about our website content

Our free and premium content is non-exclusive, meaning you are not the only one with access to the content. You can customize our content to fit your end product. Redistribution of our content is strictly prohibited. This means you cannot make our content available to others as-is, stand-alone products or stock products in ANY LANGUAGE, regardless if you offer our content for free or not.

  • Code Queen Facebook Group Icon
  • Facebook Logo
  • Twitter Logo
  • YouTube logo
  • Instagram Logo
  • Linkedin Logo

© 2021.  All Rights Reserved by Code Queen, LLC.