API - An application programming interface is a connection between computers or between computer programs. It is a type of software interface, offering a service to other pieces of software. A document or standard that describes how to build such a connection or interface is called an API specification [wikipedia].
It's a popular resource for developers in tapping external databases. A staple of every web application consumables. It may sound high-tech. But, the application is a very foundational and with the right approach and basic database knowledge, it is a very powerful tool.
API is most of the time offered for free or premium to developers. Depending on the application and level of data exposure, sometimes this is very expensive. But, for hobbyist and early stage learners. There's ready platform we can take advantage and host your own API.
Here comes Google Sheets
Google sheets it's not just a spreadsheet. Just take off your mind on the Microsoft Suite comparison. Google Apps offers great flexibility in terms of customizing your sheets application. In this article I will show how you make use of sheets as database for your API.
In this application, I will use my existing API integration with NASA API on Mars rover weather collection in Mars. This will not cover the process where I tap the Mars Weather API and pulling them into a spreadsheet. Please see Mars Weather and how to fetch the data for understanding how I was able to pull the resources automatically.
Sample Calls:
Manifest - available api syntax, definition and URLs [ https://script.googleusercontent.com/macros/echo?user_content_key=Aqvh_KWEIkA2y72sJUDkCq3hU0qyXQJnj2MY29elW17YkqH_9WqrLKEd2A_dpFBZCGVVXo3_52JBGkEeehzmavQ-ppmUFbCam5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnNqjJ-H5eu9M7hqa9rZt3J7OQe700Q-3vo4cEmcA_c3tBQ5jxh7l-ylMvMB8kT3jmtDBYm40MhAYFuROh1Y-nIh_GIAPxO1ID8oZHxDdzmO3C_sCiQ09SOE&lib=MW1B9XzBuEwJYul5IBb74tZFLmJLFTjNj]
Here's the Script:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * ROVER ENVIROMENTAL MONITORING STATION (REMS) | |
| * CUSTOM API. TO PROCESS FURTHER AND SOMEHOW RECREATE THE WEB STRUCTURE AS SHOWN ON THIS PAGE - https://mars.nasa.gov/msl/spacecraft/instruments/rems/ | |
| * CHECK THE SCRIPT USED ON THE SAME WEBSITE/PAGE = https://mars.nasa.gov/js/general/weather/msl-weather.js | |
| */ | |
| function doGet(e){ | |
| var manifest = e.parameter.manifest; | |
| var rover = e.parameter.rover; | |
| var sol = e.parameter.sol; | |
| var min_sol = e.parameter.min_sol; | |
| var max_sol = e.parameter.max_sol; | |
| if(manifest === 'yes'){ | |
| data = fetch_manifest(); | |
| }else{ | |
| if(rover === 'Curiosity'){ | |
| data = fetch_rover_data(rover,sol,min_sol,max_sol); | |
| } | |
| } | |
| return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON); | |
| } | |
| /** | |
| * MANIFEST - GENERAL INFORMATION ABOUT ROVER REMS DATA CALL. | |
| * RETURNS ROVERS INCLUDED IN THE API AND ITS CORRESPONDING PARAMETERS NEEDED/AVAILABLE FOR QUERY | |
| * RETURNS MAX SOL OR LATEST SOL CAPTURED | |
| * RETURNS SUPPORTED CALLS: | |
| * MANIFEST : https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?manifest=yes | |
| * ALL ROVER REMS : https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?rover=Curiosity | |
| * ROVER REMS BY SOL : https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?rover=Curiosity&sol=9 | |
| * ROVER REMS BY SOL RANGE : https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?rover=Curiosity&min_sol=1&max_sol=15 | |
| */ | |
| function fetch_manifest(){ | |
| var db_msl = ws_msl.getDataRange().getValues(); | |
| var db_percy = ws_percy.getDataRange().getValues(); | |
| var max_sol_msl = db_msl[db_msl.length - 1][2]; | |
| var max_sol_percy = db_percy[db_percy.length - 1][2]; | |
| return {'rovers':{'Curiosity':{'max_sol':max_sol_msl,'PARAMETERS':['rover','sol']}, | |
| 'Perseverance':{'max_sol':max_sol_percy,'PARAMETERS':['rover','sol']}}, | |
| 'sample_calls':{'manifest':'https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?manifest=yes', | |
| 'rover_all_rems':'https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?rover=Curiosity', | |
| 'rover_rems_by_sol':'https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?rover=Curiosity&sol=9', | |
| 'rover_rems_by_sol_range':'https://script.google.com/macros/s/AKfycbxrzftXp8pzZTQJFrlkf8Y8ekRpagchSuqVwpt8kb-1EagAbd9vXOcqvIMFcbJfHT0F/exec?rover=Curiosity&min_sol=1&max_sol=15'}}; | |
| } | |
| /** | |
| * THIS IS A FUNCTION TO EXTRACT FROM DB THE CORRESPONDING ROVER REMS DATA | |
| * CALL BY ROVER NAME | |
| * CALL BY ROVER NAME AND SOL | |
| * CALL BY SOL RANGE (MIN_SOL AND MAX_SOL) | |
| */ | |
| function fetch_rover_data(rover,sol,min_sol,max_sol){ | |
| var db; | |
| if(rover === 'Curiosity'){ | |
| db = ws_msl.getDataRange().getValues();//GET DATA | |
| } | |
| db.shift();//REMOVES HEADER | |
| return fetch_weather_api_data(db,sol,min_sol,max_sol);//RETURNS DATA | |
| } | |
| /** | |
| * THIS WILL FETCH THE CORRESPONDING REMS DATA | |
| * EACH ROW IS A SOL WEATHER INFORMATION | |
| */ | |
| function fetch_weather_api_data(ws_db,sol,min_sol,max_sol){ | |
| //FETCH BY SOL | |
| if(sol != null){ | |
| ws_db = ws_db.filter(function(el){ | |
| return Number(el[2]) === Number(sol); | |
| }); | |
| } | |
| //FETCH BY SOL RANGE | |
| if(min_sol != null && max_sol != null){ | |
| ws_db = ws_db.filter(function(el){ | |
| return (Number(el[2]) >= Number(min_sol) && Number(el[2]) <= Number(max_sol)); | |
| }); | |
| } | |
| //ASSEMBLE JSON STRING BY EACH ROW OF REMS WEATHER DATA | |
| ws_db.map(function(el){ | |
| var row = { | |
| 'id':el[0], | |
| 'terrestrial_date':el[1], | |
| 'sol':el[2], | |
| 'ls':el[3], | |
| 'season':el[4], | |
| 'min_temp':el[5], | |
| 'max_temp':el[6], | |
| 'pressure':el[7], | |
| 'pressure_string':el[8], | |
| 'abs_humidity':el[9], | |
| 'wind_speed':el[10], | |
| 'wind_direction':el[11], | |
| 'atmo_opacity':el[12], | |
| 'sunrise':el[13], | |
| 'sunset':el[14], | |
| 'local_uv_irradiance_index':el[15], | |
| 'min_gts_temp':el[16], | |
| 'max_gts_temp':el[17] | |
| }; | |
| api_data.push(row);//COMBINES ALL JSON STRING REMS WEATHER DATA | |
| }); | |
| //API CALL JSON RETURN | |
| return {'desclaimer':'I do not own the data. You are using the data being pulled from my personal script that logged daily weater data from REMS as published on this url(\'https://mars.nasa.gov/rss/api/?feed=weather&category=msl&feedtype=json\'. There could be mismatch from actual data and delay expected as my custom script is dependent on the original api from NASA. Use at your discretion. As a hobbyist this was for education purpose. Thank you!info@trackingperseverance.com)','data':api_data}; | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {"rovers":{"Curiosity":{"max_sol":3218,"PARAMETERS":["rover","sol"]},"Perseverance":{"max_sol":184,"PARAMETERS":["rover","sol"]}},"sample_calls":{"manifest":"https://script.google.com/macros/s/AKfycbwV7SeFLW_LM0NDfZNVHyDYss8Cq7RSGMMnicX3Jo1aGsasaxxbkWzqiR3DAMyHwE8N/exec?manifest=yes","rover_all_rems":"https://script.google.com/macros/s/AKfycbwV7SeFLW_LM0NDfZNVHyDYss8Cq7RSGMMnicX3Jo1aGsasaxxbkWzqiR3DAMyHwE8N/exec?rover=Curiosity","rover_rems_by_sol":"https://script.google.com/macros/s/AKfycbwV7SeFLW_LM0NDfZNVHyDYss8Cq7RSGMMnicX3Jo1aGsasaxxbkWzqiR3DAMyHwE8N/exec?rover=Curiosity&sol=9","rover_rems_by_sol_range":"https://script.google.com/macros/s/AKfycbwV7SeFLW_LM0NDfZNVHyDYss8Cq7RSGMMnicX3Jo1aGsasaxxbkWzqiR3DAMyHwE8N/exec?rover=Curiosity&min_sol=1&max_sol=15"}} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {"desclaimer":"I do not own the data. You are using the data being pulled from my personal script that logged daily weater data from REMS as published on this url('https://mars.nasa.gov/rss/api/?feed=weather&category=msl&feedtype=json'. There could be mismatch from actual data and delay expected as my custom script is dependent on the original api from NASA. Use at your discretion. As a hobbyist this was for education purpose. Thank you!info@trackingperseverance.com)","data":[{"id":1,"terrestrial_date":"2012-08-06T16:00:00.000Z","sol":1,"ls":150,"season":"Month 6","min_temp":"--","max_temp":"--","pressure":"--","pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:30","sunset":"17:22","local_uv_irradiance_index":"--","min_gts_temp":"--","max_gts_temp":"--"},{"id":232,"terrestrial_date":"2012-08-14T16:00:00.000Z","sol":9,"ls":155,"season":"Month 6","min_temp":"--","max_temp":"--","pressure":"--","pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:28","sunset":"17:22","local_uv_irradiance_index":"--","min_gts_temp":"--","max_gts_temp":"--"},{"id":2,"terrestrial_date":"2012-08-15T16:00:00.000Z","sol":10,"ls":155,"season":"Month 6","min_temp":-75,"max_temp":-16,"pressure":739,"pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:28","sunset":"17:22","local_uv_irradiance_index":"Very_High","min_gts_temp":-83,"max_gts_temp":8},{"id":13,"terrestrial_date":"2012-08-16T16:00:00.000Z","sol":11,"ls":156,"season":"Month 6","min_temp":-76,"max_temp":-11,"pressure":740,"pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:28","sunset":"17:21","local_uv_irradiance_index":"Very_High","min_gts_temp":-83,"max_gts_temp":9},{"id":24,"terrestrial_date":"2012-08-17T16:00:00.000Z","sol":12,"ls":156,"season":"Month 6","min_temp":-76,"max_temp":-18,"pressure":741,"pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:28","sunset":"17:21","local_uv_irradiance_index":"Very_High","min_gts_temp":-82,"max_gts_temp":8},{"id":35,"terrestrial_date":"2012-08-18T16:00:00.000Z","sol":13,"ls":157,"season":"Month 6","min_temp":-74,"max_temp":-15,"pressure":732,"pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:28","sunset":"17:21","local_uv_irradiance_index":"Very_High","min_gts_temp":-80,"max_gts_temp":8},{"id":46,"terrestrial_date":"2012-08-19T16:00:00.000Z","sol":14,"ls":157,"season":"Month 6","min_temp":-74,"max_temp":-16,"pressure":740,"pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:27","sunset":"17:21","local_uv_irradiance_index":"Very_High","min_gts_temp":-82,"max_gts_temp":9},{"id":57,"terrestrial_date":"2012-08-20T16:00:00.000Z","sol":15,"ls":158,"season":"Month 6","min_temp":-78,"max_temp":-15,"pressure":740,"pressure_string":"Lower","abs_humidity":"--","wind_speed":"--","wind_direction":"--","atmo_opacity":"Sunny","sunrise":"5:27","sunset":"17:21","local_uv_irradiance_index":"Very_High","min_gts_temp":-82,"max_gts_temp":8}]} |
No comments:
Post a Comment