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.

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

2. Input the value (candidate id) from A1: A20 in sheet tab "id_list"

3. Iterate the ID list to make the API call, and retrieve data of candidate's name, party and office.

4. 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)
  
}