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.

1. API call use “candidate” in OpenFEC Developer page, and the first API builder “/candidate​/{candidate_id}​/”

2. Input the value (candidate id) from A2 in sheet tab "person"

3. Make the API call and retrieve data of candidate's name, party and office.

4. Output the data to sheet tab "live".

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
}