Is an external API possible on Google Spreedsheet?

Question

I created a Google spreadsheet with five columns;

As soon as the user has filled in the values ​​in the first three columns, he should call a third-party API and fill in the value (response) in the fourth and fifth columns.

Is it possible to write code in Google Spreadsheet to call the API? Is it possible to call and receive a response from an external API in Google Spreadsheet?

Answer 1

There is a way to make API calls and get the results in a spreadsheet - the only way I know this is to create / open the target table, go to the tools, and then to the Script editor and use this as a related 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); } 

He needs a small interface, but it works! It takes information from an API call and places it in a spreadsheet.

Answer 2

I recently came up with the same requirement to read sheet rows and send data to the request and write the response. I thought I would share what I developed after I worked a little ...

 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 data on a worksheet, 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 this is useful for anyone who encounters a similar requirement, as described above.

I posted this script on github if you want to use fork / pull ...

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

Greetings

Parth