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
Example:-
my google sheet URL is
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
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 codeasync 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 inhttps://github.github.io/fetch/to use fetch in Wix siteyou will need to add this one line on top of the code
import {fetch} from 'wix-fetch';
// code explnationasync 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: "[email protected]"
// 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
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
Official Code Queen Partner
Stuck on a project? Hire Salman!
Email: [email protected]
Share this post