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{your google Sheet ID}/edit#gid=0


my google sheet URL is

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{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 code
async function getSheet(sheetId, sheetNumber = 1) {
 let url = `${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;
 return json;

.then(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 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

// 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 = `${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