---
description: >-
  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.
---

# Example 2: Extract Data by A Cell Range

1. API call use  “**candidate**” in [OpenFEC Developer page](https://api.open.fec.gov/developers/), 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".



```javascript
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)
  
}

```
