Setup: Google Spreadsheet & Google Apps Script

(1) First create a spreadsheet. 

Open sheets main page:

Click on “+” Icon.

Note: You can also create it from google drive main page:

Click on “+ New” Icon and then click on “Google Sheets”.

Now a default blank spreadsheet will be opened.

(2) Rename spreadsheet (like “mysheet”) from Untitled spreadsheet.

(3) Now click on “Tools” and then click on “script editor”.

(4) Rename project name (like “gsjson”) from Untitled project and then remove existing demo code from the editor.

(5) Now copy-paste code from a google-apps-script-code.txt file to here.

Note: If you have multiple sheets in a spreadsheet and want to target other sheet like Sheet2 or if you have renamed a sheet name from Sheet1 to anything else then you need to change sheet name in the script code line number: 9

  var dataSheet = ss.getSheetByName('Sheet1');


Click on a save project icon and then after click on “Deploy” button. Here select “New deployment”.

(7) Now choose “Web app” as select type. If you want you can change description (like gsjsonv1).

Choose Web app Execute as: Me & Who has access: Anyone

Then click on “Deploy” button.

(8) Now authorize access screen will be displayed like below. Click on it and select your google account.

Now Google hasn’t verified this app warning will be shown there. Click on Advanced and then click on Go to link.

Then click on “Allow“.

(9) Now you will see deployment details like Web app URL as shown in the image below:

Here copy Web app URL and paste it in html file where it says: PUT_YOUR_JSON_API_URL_HERE

Click on “done” button. That’s it.