1 of 6

¶ REST API

API: Application Programming Interface “An API is a set of definitions and protocols for building and integrating application software.”

REST API: “Representational State Transfer API”

API is basically a set of functions and procedures that allow one application to access the features of other applications, REST is an architectural style for networking applications on the web. It is limited to client-server based applications. REST is a set of rules or guidelines to build a web API. There are many ways to build a web API, and REST is a standard way that will help in building it faster and also for third-parties to understand it better.

How does REST API work?

API - Application Programming Interface. REST API - Representational State Transfer API. SERVER: the place has the resources. Client call (API call) -> Server - Data back over HTTP protocol. API Key - interface to identify its user, developer or calling program to a website, which is normally used to assist in tracking and controlling how the interface is being utilized.

Four API Call Types:

Post: Create
Get: Read
Put: Update
Delete: Delete

Compose the API Call through https protocol:

/Resource/ {Required parameter} (Optional parameter) API Key

JSON File

What is JSON file format?

JavaScript Object Notation
JSON is a syntax for storing and exchanging data
Main data format for API data exchange.

Get Started with Google Sheets Script Editor

1. Go to Tools -> Script editor to get started with your function

2. Enter your script within the function

Note: For the first time to run the function, you need to give permission to this script through your designated Google account.

Example 1: Extract Data by One Cell

Use one candidate ID from a sheet tab to retrieve name and party, and office information through an API call, and output results to another sheet tab.

API call use “candidate” in , and the first API builder “/candidate/{candidate_id}/”
Input the value (candidate id) from A2 in sheet tab "person"
Make the API call and retrieve data of candidate's name, party and office.
Output the data to sheet tab "live".

Example 2: Extract Data by A Cell Range

Use a list of candidate IDs from a range to retrieve name and party, and office information through an API call, and output results to another sheet tab.

API call use “candidate” in OpenFEC Developer page, and the first API builder “/candidate/{candidate_id}/”
Input the value (candidate id) from A1: A20 in sheet tab "id_list"
Iterate the ID list to make the API call, and retrieve data of candidate's name, party and office.
Output the data of 20 candidates to sheet tab "result".

function CandidateData() {
  //API Key
  var key = "YOUR API KEY"
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var wsResult = ss.getSheetByName("result")  //Setup Output Sheet 
  var sheet = ss.getSheetByName('id_list').getRange("A1:A20").getValues()  //Setup Input Sheet & Data range

  var toAddTable =[]    
  for (i = 0; i < sheet.length; i++) {
    //console.log(sheet[i][0])
    var toAddArray = []
    var name = sheet[i][0]

    let apiURL = 'https://api-stage.open.fec.gov/v1/candidate/'+ name + '/?api_key=' + key
    var resText = UrlFetchApp.fetch(apiURL).getContentText()
    var resJSON = JSON.parse(resText)

    var record = resJSON["results"][0]
    var can_name = resJSON["results"][0]["name"]
    var party = resJSON["results"][0]["party_full"]
    var office_f = resJSON["results"][0]["office_full"]

  //**********Add Values to the Created Table Array ********//
    toAddArray.push(can_name)
    toAddArray.push(party)
    toAddArray.push(office_f)
    toAddTable.push(toAddArray)
    }
  //console.log(toAddTable)
  wsResult.getRange("A2:C21").setValues(toAddTable)
  
}

Example 2: Extract Data by A Cell Range

Use a list of candidate IDs from a range to retrieve name and party, and office information through an API call, and output results to another sheet tab.

API call use “candidate” in OpenFEC Developer page, and the first API builder “/candidate/{candidate_id}/”
Input the value (candidate id) from A1: A20 in sheet tab "id_list"
Iterate the ID list to make the API call, and retrieve data of candidate's name, party and office.
Output the data of 20 candidates to sheet tab "result".

function CandidateData() {
  //API Key
  var key = "YOUR API KEY"
  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var wsResult = ss.getSheetByName("result")  //Setup Output Sheet 
  var sheet = ss.getSheetByName('id_list').getRange("A1:A20").getValues()  //Setup Input Sheet & Data range

  var toAddTable =[]    
  for (i = 0; i < sheet.length; i++) {
    //console.log(sheet[i][0])
    var toAddArray = []
    var name = sheet[i][0]

    let apiURL = 'https://api-stage.open.fec.gov/v1/candidate/'+ name + '/?api_key=' + key
    var resText = UrlFetchApp.fetch(apiURL).getContentText()
    var resJSON = JSON.parse(resText)

    var record = resJSON["results"][0]
    var can_name = resJSON["results"][0]["name"]
    var party = resJSON["results"][0]["party_full"]
    var office_f = resJSON["results"][0]["office_full"]

  //**********Add Values to the Created Table Array ********//
    toAddArray.push(can_name)
    toAddArray.push(party)
    toAddArray.push(office_f)
    toAddTable.push(toAddArray)
    }
  //console.log(toAddTable)
  wsResult.getRange("A2:C21").setValues(toAddTable)
  
}

function CandidateData() {
  
  var key = "YOUR API KEY"    //API Key
  var ss = SpreadsheetApp.getActiveSpreadsheet()    //Assign Google Sheets (including all tabs) to varialbe ss
  var wsName = ss.getSheetByName("person")    //Assign tab "person" to varialbe wsName
  var name = wsName.getRange("A2").getValue()  ////Extract candidate id from tab "person"

  let apiURL = 'https://api-stage.open.fec.gov/v1/candidate/'+ name + '/?api_key=' + key
  var resText = UrlFetchApp.fetch(apiURL).getContentText()   //Make the API call to extract data in JSON file format
  var resJSON = JSON.parse(resText)      //Parse JSON data

  var record = resJSON["results"][0]
  var can_name = resJSON["results"][0]["name"]                         
  var party = resJSON["results"][0]["party_full"]
  var office_f = resJSON["results"][0]["office_full"]


  //*****Output Results**********//
  var wsLiveData = ss.getSheetByName("live")   //**SETUP OUTPUT SHEET

  var candidate_name =  wsLiveData.getRange("A2")    //SETUP A2 for Candidate Name
  var party_full = wsLiveData.getRange("B2")         //SETUP B2 for Party Name
  var office_full = wsLiveData.getRange("C2")        //SETUP C2 for Office 

  candidate_name.setValue(can_name)               //SEND extracted value to var candidate_name
  party_full.setValue(party)                      //SEND extracted value to var party_full
  office_full.setValue(office_f)                  //SEND extracted value to var office_full
}