This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap
Hello, learners!
It is my pleasure to share this article with all of you who are looking for well scripted Web API (Application programming interface) in order to retrieve JSON data from the Google Spreadsheet.
Prior to that, we will see how this Web API project can be helpful and how we can strengthen our knowledge of Google App Script programming.
What are the benefits of Google Apps Script Web API?
- You can display Spreadsheet data on your website using a Web API.
- Insert or migrate JSON data using the Web API method.
- With Google Apps Script Web API, JSON data can be displayed in Dashboard representation.
- Web APIs allow for code re-usability on a website.
- Google Spreadsheet data can be displayed live on a website.
Follow these steps to find out how we can accomplish this task.
Step 1: Open Google App Script editor in our Google Spreadsheet.
Navigation: In menu options Extensions=>Apps Script
By clicking on Apps Script option, Script editor will be opened.
Step 2: At the beginning of the editor, define the doGet() function. Let’s see the sample code with explanation for how this function is used to get web hook requests from any website or webpage to send data or receive parameters from web API URL.
function doGet() {
var data = getJsonData();
return buildSuccessResponse(data, 1);
}The getJsonData() function is called to get data in JSON format in the sample code above. This function will be defined in the explanation code. The buildSuccessResponse function is used to send responses in JSON format to the Web API.
Step 3: Your spreadsheet details should be defined in the getJsonData() function. See the complete code below.
function getJsonData(){
var spreadSheetId='YOUR_SPREADSHEET_ID';
var ss = SpreadsheetApp.openById(spreadSheetId); var sheet = ss.getSheetByName("Validation_sheet");
var json_data = getData(sheet);return json_data;
}
To use the given sample code, you need to put your spreadsheet id, which can be copied from the spreadsheet URL after /d/ this. In next line is your sheet name, from which you would like to get data. After taking data from the sheet you need to pass it through getData() function, which is nothing but give you JSON array of your spreadsheet data.
Step 4: Now we need to define getData() function, which is very easy and simple. Let’s see the code for the same.
function getData(sheet){
var jo = {};
var i =0;
var rows2 = sheet.getDataRange().getValues();
Logger.clear();
// ===============Get data=================
var rows2=[];
var rangeData = sheet.getDataRange();
var values = rangeData.getValues();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();// Logger.log("lastrow=>"+ lastRow);
var data=[];
var x=0,y=0;
for(i = 0; i<lastRow; i++){
data[x]=[];
for(var j=0; j<lastColumn; j++ ){
if(email == values[i][2]){
data[x][j]= values[i][j];
y=1;
}
}
if(y==1){ x=x+1; y=0; }
}
return data;
}In above declared function, we are just have defined few parameters as follows: range of the sheet, last row, last column and the values of the range in array form. Here you can filter the data based on conditions as per requirement, other wise function will return complete range values in JSON format.
Step 5: Now, we come to our last part of the Web API Google Script code. We just need to defined buildSuccessResponse() function, which was declared earlier inside of the doGet() function at the top of the sample code.
function buildSuccessResponse(data, pages) {
var output = JSON.stringify({
status: 'success',
data: data,
pages: pages
});
return ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JSON);
}In this code, we are passing whole array which was taken from the getJsonData() function above. Array data getting stringnify which is the just a process to get array data in JSON format and with that we can also send some parameters like if data is sending successfully to Web API then pass parameter success with value success, similarly you pass more parameters.
I hope this article helped you to build your Web API in order to get the data from your Google Spreadsheet to the Webpages. If you need any help or would like to customize this code as per requirements then you may write to me on this twitter link.
To help others please share this article with them and to support us please do upvote this article. Thanks Happy Learning :)
How to build a Web API that retrieves JSON data from a Google Spreadsheet using Google Apps Script was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Dilip Kashyap
Dilip Kashyap | Sciencx (2022-01-10T12:53:09+00:00) How to build a Web API that retrieves JSON data from a Google Spreadsheet using Google Apps Script. Retrieved from https://www.scien.cx/2022/01/10/how-to-build-a-web-api-that-retrieves-json-data-from-a-google-spreadsheet-using-google-apps-script/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.