Accessing NASA API with Google Sheets

A few days ago I learnt via Twitter that NASA has opened up its API and allows developers, technologists and even educators, access to its massive (and live) collection of data and imagery. To test out the feature’s basics I tried writing a small Google Sheets script that connects to the image galleries of Mars Rovers – Curiosity, Opportunity and Spirit.

What is an API?

So before we go into what I did, let us take a quick look at what an API is. API stands for Application Programming Interface. Simply put, it is a way for you as a user to interact directly with data from a specific website via dedicated (and secure) links without having to leave your own environment. Every time you go to these dedicated links, you get a mass chunk of data that then needs to be cleared up so you can use them for various purposes. I found this video on YouTube that explains this process well.

STEP 1: API documentation

So with that basic definition out of the way, let us start digging a little deeper. The first thing to do is look up the documentation provided by the website (in this case NASA) that tells you exactly how to access their API content. Since I focused on Mars Rover imagery, I went directly to that section and found this.

Source :

These links tell me that to access the locations on their API for different kinds of rovers, I will have to following the path like this:

Or, in other words,<<rover name>>/photos?sol=<<sol number>>&camera=<<camera code>>&api_key=DEMO_KEY

So if I change the values for the rover name, sol number and camera code I would get different chunks of data that contain images taken by that particular Mars rover on that specific Martian day (sol) with that specific camera. Cool!

The website also highlights the different codes and values required for these parameters. So it is worth spending some time familiarizing oneself with it.

STEP 2: Setting up the Google sheet

Since I wanted to try it out first in a simple sheet (I am also working on an interactive web page that has similar controls but with better interactivity) I wanted to set up all the required parameters on my sheet. That looked like this.

Google Sheet with the values I need.

As you can see the sheet has some basic drop downs. Rover name, Camera name, Martian Sol number and Earth Date. The goal now is to write a script that will take these values and push it to the NASA API link that pulls out the required data for me to clear up and display.

STEP 3: The script

The final piece of this is the script that actually does all the work. The breakdown of what the script is doing is this:

  • Read the values in the specific cells of the sheet.
  • Pass them to the API link shown above.
  • Read the API data into JSON (JavaScript Object Notation) which makes it easy for us to read the content.
  • Parse the data to pull out specific fields required for my display of images matching my criteria of rover name, sol number etc.
  • Display it on the sheet.

I know, I know. This all sounds like a lot of work. It gets better with practice like most things! So here is the code itself.

function marsRover() { //function starts here
    var sheet = SpreadsheetApp.getActive(); //open sheet
    var range = sheet.getRange("F4:F1000"); 
   range.clearContent(); //clear up results area
  //reading sheet values now.
  var camera = sheet.getRange("B4").getValue(); 
  var sol = sheet.getRange("C4").getValue();
  var earthDate =   sheet.getRange("D4").getValue();
  var code = sheet.getRange("E4").getValue();
  //convert date to format that NASA API requires (yyyy-MM-dd)
  earthDate = Utilities.formatDate(new Date(earthDate), "GMT+0300", 'yyyy-MM-dd')
  var roverName = sheet.getRange("A4").getValue();
 //push the value URL now to the NASA link and fetch response.
  var response = UrlFetchApp.fetch(""+roverName+"/photos?earth_date="+earthDate+"&sol="+sol+"&camera="+code+"&api_key=DEMO_KEY");
  //read the result into a JSON variable
  var json = response.getContentText();
  var data = JSON.parse(json); //clean up it a little 
  //load all photo array elements
  var photos = data["photos"];
  var output = [];
  //loop through photo array and select image URLs that match
  photos.forEach(function(elem,i) {
   var image = elem["img_src"];
    output.push(image); //store them into output
var len = photos.length; //to display count of matches

sheet.getRange("F4").setValue(len + " matches");
var startRow =5; //formatting results
for(var index=0;index<len;index++) //loop through array
sheet.setRowHeight(startRow, 170);
sheet.getRange("F"+startRow).setValue("=HYPERLINK(\""+output[index]+"\";IMAGE(\""+output[index]+"\"; 4,175,175))")
//set up images and link them to actual image on NASA website

}//end of photo for loop
} //end of function

The code can be a bit overwhelming for people not familiar with it but it is worth looking at closely since it holds all the required pieces.

The JSON data that was picked up by the API looks like this.

As you can see its quite a bit of data that came out depending on what was sent to the link. Parsing JSON fetch data takes some work so it maybe a good idea to look up some help on how to read the JSON data that is fetched. JSON Formatter is a good tool to help you breakdown what was fetched into more meaningful chunks. I used it on this example and I got this result.

You can already tell that there is a structure and a clear format that needs to be followed to read this data, right? This is why using a good JSON formatter is ideal. This shows me that img_src within the photos array is the field I need to extract. As you see in my script, that is what I did.

STEP 4: The Output

The final step, as it were, is testing this all out. I added a sheet trigger that runs this function every time there is an edit to the values. Since I am using drop downs on the sheet, this was ideal for this solution. Given below is the GIF of what a run looks like for this.

As you can see the parameters I am choosing are pretty random but they all are trying to access only one API link. The different values being pushed to this link allows me to fetch different chunks of JSON data that then is parsed to extract the image links I need.

This sort of a project opens up various powerful possibilities for use within a classroom. Students can write small scripts such as this to access some of the most popular and massive API databases from around the world and develop their own applications for various educational purposes! This activity combines the awesomeness of coding with the real world application of it by allowing students to experience live and authentic data from well known organizations and companies. A project definitely worth trying withing your classrooms/schools.

This is the direct link to my Google sheet. Please feel free to make a copy and use it as required.

Useful further reading