Created
May 14, 2019 23:14
-
-
Save j0nathanB/158cd794de67dd0e4c61b7df005d76c7 to your computer and use it in GitHub Desktop.
Google Sheets + Mode API
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
| function onOpen() { | |
| var ui = SpreadsheetApp.getUi(); | |
| ui.createMenu('Update Jobs') | |
| .addItem('Get New Jobs','getNewestJobs') | |
| .addToUi(); | |
| } | |
| function fetchModeData() { | |
| var options = { | |
| headers: { | |
| 'Postman-Token': 'POSTMAN-TOKEN', | |
| 'cache-control': 'no-cache', | |
| Authorization: 'AUTHORIZATION' | |
| } | |
| }; | |
| var reportUrl = 'https://modeanalytics.com/api/{org}/reports/{report}/runs/' | |
| var reportResponse = JSON.parse(UrlFetchApp.fetch(reportUrl, options)); | |
| var runToken = reportResponse._embedded.report_runs[0].token; | |
| var runUrl = 'https://modeanalytics.com/api/{org}/reports/{report}/runs/' + runToken + '/results/content.csv' | |
| var runResults = UrlFetchApp.fetch(runUrl, options).toString(); | |
| var formattedResults = runResults.split("\n").map( function(row) { | |
| return row.split(','); | |
| }).slice(1); // remove header row from results | |
| return formattedResults; | |
| } | |
| function getNewestJobs() { | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getActiveSheet(); | |
| var lastRow = sheet.getLastRow(); | |
| var modeData = fetchModeData(); | |
| var lastJobInSheet = sheet.getRange(lastRow, 1).getValue(); | |
| var lastJobInMode = modeData[0][0] | |
| var lastDate = Date.parse( sheet.getRange(lastRow, 7).getValue() ) | |
| if (lastJobInSheet !== lastJobInMode) { | |
| var results = | |
| modeData.filter( function(job) { | |
| var dateField = job[6] || "" | |
| var jobDate = Date.parse(dateField.split(' ').join('T')); | |
| if (Number(jobDate)) { | |
| return lastDate < jobDate | |
| } | |
| }).map( function(job) { | |
| return { | |
| id: job[0], | |
| url: "https://your.dashboard.com/" + job[0], | |
| title: job[1], | |
| email: job[3], | |
| team: job[4], | |
| launch: new Date(job[6].split(' ').join('T')), | |
| } | |
| }); | |
| for (var i = results.length -1; i >= 0; i--) { | |
| var job = results[i]; | |
| sheet.getRange(sheet.getLastRow() + 1, 1).setValue(job.id); | |
| sheet.getRange(sheet.getLastRow(), 2).setValue(job.url); | |
| sheet.getRange(sheet.getLastRow(), 3).setValue(job.title); | |
| sheet.getRange(sheet.getLastRow(), 4).setValue(job.team); | |
| sheet.getRange(sheet.getLastRow(), 6).setValue(job.email); | |
| sheet.getRange(sheet.getLastRow(), 7).setValue(job.launch); | |
| } | |
| } | |
| } | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment