Created
July 31, 2022 22:03
-
-
Save stanasiukcom/11a8733647432aa2bb3d379eb4937b49 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
| // Load Day.js | |
| eval(UrlFetchApp.fetch('https://unpkg.com/[email protected]/dayjs.min.js').getContentText()); | |
| eval(UrlFetchApp.fetch('https://unpkg.com/[email protected]/locale/pl.js').getContentText()); | |
| dayjs.locale('pl') | |
| eval(UrlFetchApp.fetch('https://unpkg.com/[email protected]/plugin/weekday.js').getContentText()); | |
| dayjs.extend(dayjs_plugin_weekday) | |
| eval(UrlFetchApp.fetch('https://unpkg.com/[email protected]/plugin/utc.js').getContentText()); | |
| dayjs.extend(dayjs_plugin_utc) | |
| var config = { | |
| 'stripeApiKey': '', // Insert your API key here | |
| 'reportStartingDate': dayjs().utc().weekday(-7).set('hour', 0).set('minute', 0).set('second', 0).set('milisecond', 0).unix().toString(), // Previous week's Monday | |
| 'reportEndingDate': dayjs().utc().weekday(-1).set('hour', 23).set('minute', 59).set('second', 59).set('milisecond', 0).unix().toString(), // Last week's Sunday | |
| // Enter the names of your sheets | |
| 'paymentsSheetName': 'Stripe Payments', | |
| 'subscriptionsSheetName': 'Stripe Subscriptions' | |
| } | |
| function requestPaymentsData(stripeApiKey, intervalStart, intervalEnd) { | |
| var payload = { | |
| 'report_type':'balance_change_from_activity.itemized.3', | |
| 'parameters[interval_start]':intervalStart, | |
| 'parameters[interval_end]':intervalEnd, | |
| 'parameters[columns][0]':'balance_transaction_id', | |
| 'parameters[columns][1]':'created', | |
| 'parameters[columns][2]':'currency', | |
| 'parameters[columns][3]':'gross', | |
| 'parameters[columns][4]':'reporting_category', | |
| 'parameters[columns][5]':'description' | |
| }; | |
| requestBody = Object.entries(payload) | |
| .map(([key, value]) => [encodeURIComponent(key), encodeURIComponent(value)].join('=')) | |
| .join('&'); | |
| var url = 'https://api.stripe.com/v1/reporting/report_runs'; | |
| var options = { | |
| 'method': 'post', | |
| 'headers': { | |
| 'Authorization': 'Bearer ' + stripeApiKey, | |
| "Content-Type": "application/x-www-form-urlencoded" | |
| }, | |
| 'payload': requestBody | |
| } | |
| var response = UrlFetchApp.fetch(url, options); | |
| var json = response.getContentText(); | |
| var data = JSON.parse(json); | |
| // Return report id | |
| return data.id | |
| } | |
| function getPaymentsData(stripeApiKey, id) { | |
| var getRunUrl = 'https://api.stripe.com/v1/reporting/report_runs/' + id | |
| var options = { | |
| 'headers': { | |
| 'Authorization': 'Bearer ' + stripeApiKey, | |
| } | |
| } | |
| var run = UrlFetchApp.fetch(getRunUrl, options); | |
| Logger.log('Payments report created successfully. Downloading.') | |
| var runJson = run.getContentText(); | |
| var runData = JSON.parse(runJson); | |
| Logger.log(runData); | |
| Logger.log('Downloading report: ' + runData.result.url) | |
| var report = UrlFetchApp.fetch(runData.result.url, options) | |
| return report.getBlob().getDataAsString(); | |
| } | |
| function getSubscriptionsData(stripeApiKey, reportStartingDate, reportEndingDate) { | |
| var subSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(config.subscriptionsSheetName); | |
| SpreadsheetApp.setActiveSheet(subSheet) | |
| var getSubscriptionsUrl = 'https://api.stripe.com/v1/subscriptions/search'; | |
| var query = 'status:"active"'; | |
| var payload = { | |
| 'query': query | |
| } | |
| requestBody = Object.entries(payload) | |
| .map(([key, value]) => [encodeURIComponent(key), encodeURIComponent(value)].join('=')) | |
| .join('&'); | |
| var options = { | |
| 'method': 'get', | |
| 'headers': { | |
| 'Authorization': 'Bearer ' + stripeApiKey, | |
| 'Content-Type': 'application/x-www-form-urlencoded' | |
| }, | |
| // 'payload': requestBody, | |
| 'muteHttpExceptions': true | |
| } | |
| var subscriptions = UrlFetchApp.fetch(getSubscriptionsUrl + '?query=' + encodeURIComponent(query), options); | |
| var subData = JSON.parse(subscriptions.getContentText()); | |
| var rows = []; | |
| Logger.log(subData.has_more) | |
| do { | |
| for (var i = 0; i < subData.data.length; i++) { | |
| var row = []; | |
| var sub = subData.data[i]; | |
| row.push(reportStartingDate); | |
| row.push(reportEndingDate); | |
| row.push(sub.id); | |
| row.push(sub.customer); | |
| row.push(sub.start_date); | |
| row.push(sub.cancel_at); | |
| row.push(sub.plan.amount); | |
| row.push(sub.plan.currency); | |
| rows.push(row); | |
| Logger.log(row); | |
| } | |
| subscriptions = UrlFetchApp.fetch(getSubscriptionsUrl + '?query=' + encodeURIComponent(query) + '&page=' + subData.next_page, options) | |
| subData = JSON.parse(subscriptions.getContentText()) | |
| Logger.log(subData) | |
| Logger.log(subData.has_more) | |
| } while (subData.has_more == true); | |
| var lastRow = subSheet.getLastRow(); | |
| var Avals = subSheet.getRange("A1:A"+lastRow).getValues(); | |
| var Alast = lastRow - Avals.reverse().findIndex(c=>c[0]!=''); | |
| subSheet.getRange(Alast + 1, 1, rows.length, rows[0].length).setValues(rows) | |
| } | |
| function appendCsvToSheet(csv, destinationSheet) { | |
| var csvData = Utilities.parseCsv(csv); | |
| // Remove header row | |
| csvData.splice(0, 1); | |
| SpreadsheetApp.setActiveSheet(destinationSheet) | |
| var startCol = 1; | |
| var numRows = csvData.length; | |
| var numColumns = csvData[0].length; | |
| var Avals = destinationSheet.getRange("A1:A"+destinationSheet.getLastRow()).getValues(); | |
| var Alast = +destinationSheet.getLastRow() - Avals.reverse().findIndex(c=>c[0]!=''); | |
| var startRow = Alast + 1 | |
| destinationSheet.getRange(startRow, startCol, numRows, numColumns).setValues(csvData); | |
| Logger.log('Data successfully appended to sheet: ' + destinationSheet.getName()); | |
| } | |
| function init() { | |
| // Request payments data | |
| Logger.log(config.reportStartingDate) | |
| Logger.log(config.reportEndingDate) | |
| var paymentsReportId = requestPaymentsData(config.stripeApiKey, config.reportStartingDate, config.reportEndingDate); | |
| Logger.log('Payments report requested: ' + paymentsReportId) | |
| // Wait a minute... | |
| Logger.log('Waiting...') | |
| Utilities.sleep(180000); | |
| var paymentsData = getPaymentsData(config.stripeApiKey, paymentsReportId); | |
| Logger.log('Payments data retrieved. Inserting.'); | |
| var paymentsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(config.paymentsSheetName); | |
| appendCsvToSheet(paymentsData, paymentsSheet); | |
| Logger.log('Requesting active subscriptions data.') | |
| getSubscriptionsData(config.stripeApiKey, config.reportStartingDate, config.reportEndingDate); | |
| checkSubscriptions(); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment