Instantly share code, notes, and snippets.
Last active
December 1, 2023 00:18
-
Star
1
(1)
You must be signed in to star a gist -
Fork
0
(0)
You must be signed in to fork a gist
-
-
Save tallcoleman/b5dc915292106031b6a1a8f8caef2b61 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| * Script to pull live data from BikeSpace API into Google sheets | |
| * To use, set a time trigger (e.g. to run importData every hour) | |
| * | |
| * For first time set up, you may want to set a trigger to run every 5 minutes | |
| * (Every minute is too frequent; starts pulling duplicate data) | |
| * Then let the trigger run for about an hour | |
| */ | |
| // config | |
| const api_endpoint = "https://api-dev.bikespace.ca/api/v2/submissions"; | |
| const issues = { | |
| "issue_not_provided": "not_provided", | |
| "issue_full": "full", | |
| "issue_damaged": "damaged", | |
| "issue_abandoned": "abandoned", | |
| "issue_other": "other" | |
| }; | |
| const api_page_size = 1000; // page size to request from BikeSpace API | |
| const batch_size = 100; // cap to keep larger sheet updates within GAS execution time limit; max ~200 | |
| const sheet_name = "Data"; // name of sheet to be updated | |
| /** | |
| * Function to import data from the BikeSpace API, supplement with additional parameters, and update Google Sheet | |
| * Use this one for the time-based trigger | |
| */ | |
| function importData() { | |
| // get data from API and parse | |
| let submissions = []; | |
| let page_number = 1; // API pagination uses 1-based indexing | |
| let has_next = false; | |
| do { | |
| let url = `${api_endpoint}?limit=${api_page_size}&offset=${page_number}`; | |
| let response = UrlFetchApp.fetch(url); | |
| let response_data = JSON.parse(response); | |
| submissions = submissions.concat(response_data["submissions"]); | |
| has_next = response_data["pagination"]["has_next"]; | |
| page_number = page_number + 1; | |
| } while (has_next === true); | |
| // get existing data in spreadsheet | |
| const sheet = SpreadsheetApp.getActive().getSheetByName("Data"); | |
| let sheet_data = sheet.getDataRange().getValues(); | |
| // check for new values | |
| let sheet_data_values = sheet_data.slice(1,); | |
| let sheet_data_ids = sheet_data_values.map((x) => x[1]); | |
| let new_submissions = submissions.filter((x) => !sheet_data_ids.includes(x["id"])); | |
| if (new_submissions.length == 0) return; | |
| // cap batch size (if needed for GAS execution time limit of 6 mins) | |
| const max_entries = Math.min(new_submissions.length, batch_size); | |
| // add supplemental data | |
| let data_supplemented = []; | |
| for (let key = 0; key < max_entries; key++) { | |
| Logger.log(`Adding supplemental data to entry ${key + 1} of ${max_entries} (id # ${new_submissions[key]["id"]})`); | |
| data_supplemented[key] = new_submissions[key] | |
| // format issue types | |
| data_supplemented[key]["issues"] = [ | |
| "['", | |
| data_supplemented[key]["issues"].join("', '"), | |
| "']" | |
| ].join(""); | |
| // break out issue types | |
| for (let [issue_key, issue_value] of Object.entries(issues)) { | |
| data_supplemented[key][issue_key] = new_submissions[key]["issues"].includes(issue_value); | |
| } | |
| // add ward | |
| Object.assign( | |
| data_supplemented[key], | |
| getWard(data_supplemented[key]["latitude"], data_supplemented[key]["longitude"]) | |
| ); | |
| } | |
| Logger.log(`Appending ${max_entries} rows...`); | |
| // Add headings first time | |
| if (!sheet_data[0][0]) { | |
| let headings = Object.keys(data_supplemented[0]); | |
| sheet.appendRow(headings); | |
| } | |
| // append new entries to spreadsheet | |
| let output_array = data_supplemented.map((x) => Object.values(x)); | |
| for (row of output_array) { | |
| sheet.appendRow(row); | |
| } | |
| } | |
| /** | |
| * Uses the Open North API to determine City of Toronto Ward from latitude and longitude | |
| * @param {number} lat Latitude to any number of significant digits | |
| * @param {number} long Longitude to any number of significant digits | |
| */ | |
| function getWard(lat, long) { | |
| Utilities.sleep(1000); // rate limit on API is 60 requests per minute | |
| const query = `https://represent.opennorth.ca/boundaries/toronto-wards-2018/?contains=${lat}%2C${long}`; | |
| const result = UrlFetchApp.fetch(query); | |
| const result_data = JSON.parse(result); | |
| let boundaries = Object.values(result_data["objects"]); | |
| return { | |
| "ward_name": boundaries[0]?.["name"], | |
| "ward_number": boundaries[0]?.["external_id"] | |
| }; | |
| } | |
| /** | |
| * Open North API Tests | |
| */ | |
| function testGetWard() { | |
| let toronto = { | |
| "lat": 43.64854243, | |
| "long": -79.39336487 | |
| }; | |
| let india = { | |
| "lat": 16.5409064, | |
| "long": 80.1749461 | |
| }; | |
| Logger.log(getWard(india.lat, india.long)); | |
| Logger.log(getWard(toronto.lat, toronto.long)); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment