# Retrieving data from a public Google Spreadsheet and display it on a webpage

One of the most sought after tasks to create for someone, is the backend for a simple, mostly-static website for which the website owner wants control over is, editing content. So the first thing that comes to mind to almost every developer is WordPress ! At this point in time, no one will go to the extend of creating a custom backend, CMS like system for a website on a very tight budget.

Now imagine telling your client to enter some of the website's data as text into a Google Spreadsheet ! Even to the extent of having multiple sheets within the Spreadsheet - one sheet representing each page or section of the website. That would streamline the process of not having a regular backend database like MySQL or so, that your client need not have training in using a CMS like WordPress or the overhead of setting up a database platform just to publish some 10 page static website. As a website designer / front-end developer, you can focus on the aesthetics and deploy using a [Jamstack](https://jamstack.org/) architecture.

Create a Google Spreadheet with the FAQ data in the first and second columns and *share* it with **Anyone with the link**.

![anyone-with-link.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1660961828021/HkH41YZ1n.png align="left")

I've created a FAQ and shared (publicly viewable) Google Spreadsheet at : 
https://docs.google.com/spreadsheets/d/1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo/edit#gid=0

The FAQ content is taken from razorui.com, a cool UI toolkit based on TailWindCSS.

The URL output as JSON is :
https://docs.google.com/spreadsheets/d/1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo/gviz/tq?tqx=out:json&tq&gid=0

The JSON content received will start like this :
```
/*O_o*/
google.visualization.Query.setResponse(
```
and ends in :
```
);
```
The actual JSON is within.
Thereby trimming the first 47 characters and the last 2 characters
`substring(47).slice(0, -2)`

So, we generate the URL and `fetch` the content and then compile the HTML in the function `getFAQ`

```
// This is the SpreadSheet ID
var id = '1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo';

// This is the sheet ID - you can reference different sheets in the spreadsheet with this
var gid = '0';

// Final URL : https://docs.google.com/spreadsheets/d/1zwblTR5DWzgOiVxsDYJJkP5Gcmi4OGiCtldI0nyxZGo/gviz/tq?tqx=out:json&tq&gid=0
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;

console.log(url);

fetch(url)
.then((response) => response.text())
.then((data) =>
{
    let json_string = data.substring(47).slice(0, -2);
    let details = getFAQ(JSON.parse(json_string));
    document.getElementById("razorui-faq").innerHTML = details;
});

function getFAQ(json)
{    
    let details = '';

    json.table.rows.forEach((row, i) =>
    {
        if (i == 0) return; // The first row is the header

        details += '<details>';

        try { var value = row.c[0].f ? row.c[0].f : row.c[0].v }
        catch(e){ var value = '' }

        details += '<summary>' + value + '</summary>';

        try { var value = row.c[1].f ? row.c[1].f : row.c[1].v }
        catch(e){ var value = '' }

        details += '<p>' + value + '</p>';

        details += '</details>';
    });

    return details;
}
```

Now imagine you developing a 10-page static website, for a client with a really low-budget, using [NextJS and exporting as HTML](https://nextjs.org/docs/advanced-features/static-html-export) and then deploying your NextJS website to [GitHub using gh-pages](https://wallis.dev/blog/deploying-a-next-js-app-to-github-pages) for free !

Credit to Google Sheets Expert [Mike Steelson](https://stackoverflow.com/users/15019380/mike-steelson) for providing the solution.

Full Demo : https://anjanesh.s3.amazonaws.com/demo/faq.html