Parse JSON from API to updating Google Sheet
Budget: $10 – $30 USD
I need a Google Apps script (https://developers.google.com/apps-script/overview) written (in JavaScript) that will call a REST API, return two JSON files, and parse them to separate tabs on a Google Sheet.
Here are some sample methods of calling APIs, returning JSON and parsing to Google Sheets for your reference:
https://github.com/bradjasper/ImportJSON
https://developers.google.com/apps-script/guides/services/external
https://codewithcurt.com/how-to-call-json-web-service-to-populate-google-sheet/
I need the script to refresh the JSON with a new API call every 10 minutes on election night. I can't share the API key with you for security reasons, but here is some reference materials if you need them (https://aphelp.ap.org/Content/SupportDocs/Elections/API/#t=Welcome.htm). The calls I will be making will return updated versions of the two JSONs I have attached/uploaded and I need the resulting Google Sheet to be parsed, formatted and populated as is the attached Excel file.
The API calls that returned those JSONs were:
https://api.ap.org/v2/elections/2016-11-08?apikey={{API KEY}}&format=XML&level=STATE&officeID=H
and
https://api.ap.org/v2/elections/2016-11-08?apikey={{API KEY}}&format=XML&level=STATE&officeID=S
Note: I have tried a few methods myself and have run into a problem where the parsing of the JSON file only pulls one candidate for each race. I need all candidates included in the JSON to be parsed to the Google Sheet. I was able to pull all of them using a website JSON to CSV converter by "pivoting data instead of flattening it." So that sort of change to the code might be the solution.
Here are some sample methods of calling APIs, returning JSON and parsing to Google Sheets for your reference:
https://github.com/bradjasper/ImportJSON
https://developers.google.com/apps-script/guides/services/external
https://codewithcurt.com/how-to-call-json-web-service-to-populate-google-sheet/
I need the script to refresh the JSON with a new API call every 10 minutes on election night. I can't share the API key with you for security reasons, but here is some reference materials if you need them (https://aphelp.ap.org/Content/SupportDocs/Elections/API/#t=Welcome.htm). The calls I will be making will return updated versions of the two JSONs I have attached/uploaded and I need the resulting Google Sheet to be parsed, formatted and populated as is the attached Excel file.
The API calls that returned those JSONs were:
https://api.ap.org/v2/elections/2016-11-08?apikey={{API KEY}}&format=XML&level=STATE&officeID=H
and
https://api.ap.org/v2/elections/2016-11-08?apikey={{API KEY}}&format=XML&level=STATE&officeID=S
Note: I have tried a few methods myself and have run into a problem where the parsing of the JSON file only pulls one candidate for each race. I need all candidates included in the JSON to be parsed to the Google Sheet. I was able to pull all of them using a website JSON to CSV converter by "pivoting data instead of flattening it." So that sort of change to the code might be the solution.