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.