External API call in Google Spreedsheet is possible?

Question

I have created a Google Spreadsheet with five columns;

Once the user filled the values in the first three columns, It has to call a 3rd party API and fill the value(response) in the forth and fifth columns.

Is it possible to write a code in Google Spreadsheet for API call? Is it possible to call and get response from external API in Google Spreadsheet?

Answer 1

There's a way to make API calls and have the results go into a spreadsheet - the only way I know to do it is create/open the target spreadsheet, go to tools and then Script editor, and use this as a bound script:

function Maestro() { var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet (bound to this script) var sheet = ss.getSheetByName('mae'); //The name of the sheet tab where you are sending the info  var apiCall = 'getUpcomingConference'; var apiKey = '_____key here______'; var apiToken = '______security token______'; var url = 'http://myaccount.maestroconference.com/_access/' + apiCall +"?customer=" + apiKey + "&key=" + apiToken; //api endpoint as a string   var response = UrlFetchApp.fetch(url); // get api endpoint var json = response.getContentText(); // get the response content as text var mae = JSON.parse(json); //parse text into json  Logger.log(mae); //log data to logger  var stats=[]; //create empty array to hold data points  var date = new Date(); //create new date for timestamp  //The number in brackets refers to which instance we are looking at - soonest upcoming call is [0], next after that is [1], etc. stats.push(date); //timestamp stats.push(mae.value.conference[0].name); stats.push(mae.value.conference[0].scheduledStartTime); stats.push(mae.value.conference[0].UID);  //append the stats array to the active sheet  sheet.appendRow(stats); } 

It needs a little interface work but functions! It takes info from an API call and puts it in a spreadsheet.

Answer 2

I've recently come accross the same requirement to read the rows of the sheet and send the data in request and record the response. I thought I would share what I worked out after googling a bit...

function testing_this() {     var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();     for (row in data) {         Logger.log(data[row]);             var row = data[row]         var options = {             'method': 'post',             'payload': { email:row[1]}         };         // sending to API. for example:          UrlFetchApp.fetch('https://your-rest-api-url/v1/customers/', options);     } } 

If you want to get the data in the sheet you should use the function:

var response = UrlFetchApp.getRequest("http://your-api-url/");     for(data in response) {         var respData = response[data];         // do whatever u want to do with this data...     } 

Hope it is useful to you all who are facing similar requirement as above.

I've posted this script in github if you want to fork/pull...

https://github.com/joshiparthin/gsheetScriptExperiments/blob/master/readAndSendToApi.js

Cheers,

Parth