HOME | Mysite 11

Article link

Google sheet as JSON

Get the google sheet data in json format without using google API.

Enter your sheet ID :

OUTPUT :

Step 1 : Publish google sheet

file > Publish tothe web.... > Publish > Publish (in alert window)

Step 2 : Get google sheet ID
copy the sheet url
url : https://docs.google.com/spreadsheets/d/1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg/edit
the black color is the google sheet id - 1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg
copy only the sheet id

Step 3 : Paste the sheet ID and press "Fetch google sheet"
The json file will be shown in the user element

Wix-http-function

wanna use this feature in code directly
1. use fetch to this url change the sheet ID at the end
https://salman2301.wixsite.com/google-sheet/_functions/googleSheet/1uPAvSYtOYGUQbbRrM8Mh3gdgiUw6QhMihY6MWvvn7Jg

CODE

import { fetch } from 'wix-fetch';

$w.onReady(function () {
	$w('#btnFetch').onClick(async () => {

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) {
	// Make sure it is public or set to Anyone with link can view
	let url = `https://spreadsheets.google.com/feeds/list/${sheetId}/${sheetNumber}/public/values?alt=json`;

Disclaimer :
1. code is used only for the educational purpose.
2. Use the code at your own risk
3. Use google API to get the data more secure way