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 OpenFEC Developer page, 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".
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
}