Show Google sheet data to JSON

[vc_row type=”in_container” full_screen_row_position=”middle” scene_position=”center” text_color=”dark” text_align=”left” overlay_strength=”0.3″ shape_divider_position=”bottom”][vc_column column_padding=”no-extra-padding” column_padding_position=”all” background_color_opacity=”1″ background_hover_color_opacity=”1″ column_shadow=”none” column_border_radius=”none” width=”1/1″ tablet_text_alignment=”default” phone_text_alignment=”default” column_border_width=”none” column_border_style=”solid”][vc_column_text]

This topic is related to show the google sheet data on your website. This is only an Excel spreadsheet. You can use this sheet as a database of your website. So you don’t need any database if you are using the google sheet. As many none technical person is used the Excel sheet for storing their business data. So now you can display this data on your web page directly without any PHP, Mysql or any other database. It will return you the JSON data and you can use it on your webpage with the help of jquery.

So in this topic, we will show you how to convert google sheet data in JSON format and use this URL as a REST API.

First, you’ll have a Google account and create a new spreadsheet.

Click this link for creating the New Sheet Click Here

Now you have the below screen. Click on the Blank sheet to create a new sheet

Now you have a blank sheet. so you just need to add your data in this sheet. The first row of the sheet should be the heading of the columns. This name will be used for JSON keys,  Freeze the first row with the column names.  The column titles should only be one word, no uppercase. If you need to use multiple words for the column title then insert a hyphen between the words like the first name you should write it like first_name add under. You can view the image below for reference.

Now you need to add the script in your excel sheet. In the sheet menu, Go to Tools → Script Editor, and it will open up a code editing new window. you can view the below image for the reference

Then Paste the JavaScript from this link into the code editor. Script Code

With the script finalized, the API can be made publicly available by going to Publish > Deploy as a web app from the script editor menu bar. Ensure the app is being executed as me and that anyone, even anonymous has access.

Deploying will return a URL that will look like the below:

https://script.google.com/macros/s/{id}/exec

Append the API key to the URL and then enter it into your web browser to check that the API is working correctly. you can also get the URL after deploying a web app. you can view the below image for the reference. Add the API key in this URL any random number or string. This is for the security purpose

https://script.google.com/macros/s/{id}/exec?key=testkey

Now your JSON code is ready you can use it on your web page.

Its look like a below image

You can view this JSON on json viewer website click here

In my next article, I will show you how to Display JSON data on your web page with jquery. [/vc_column_text][/vc_column][/vc_row]

On: 03-31-2020