Created
August 1, 2022 15:43
-
-
Save stanasiukcom/3b5f9555638c141bf217803cfbd04ca7 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
| function forEachRangeCell(range, f) { | |
| const numRows = range.getNumRows(); | |
| const numCols = range.getNumColumns(); | |
| for (let i = 1; i <= numCols; i++) { | |
| for (let j = 1; j <= numRows; j++) { | |
| const cell = range.getCell(j, i) | |
| f(cell) | |
| } | |
| } | |
| } | |
| function checkSubscriptions() { | |
| const subsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stripe Subscriptions'); | |
| SpreadsheetApp.setActiveSheet(subsSheet) | |
| const values = subsSheet.getRange("A2:J"+subsSheet.getLastRow()).getValues(); | |
| let subs = {}; | |
| let weeks = [] | |
| for (row in values) { | |
| if (values[row][2].length > 0) { | |
| weeks.push(values[row][9]) | |
| if (!subs[values[row][2]]) { | |
| subs[values[row][2]] = [values[row][9]] | |
| } else { | |
| subs[values[row][2]].push(values[row][9]) | |
| } | |
| } | |
| } | |
| weeks = [...new Set(weeks)] | |
| let firstWeek = Math.min(...weeks) | |
| let lastWeek = Math.max(...weeks) | |
| values.forEach(function(row, index) { | |
| let subId = row[2] | |
| let rowWeek = row[9] | |
| let cellRow = index + 2 | |
| if (rowWeek !== firstWeek && !subs[subId].includes(rowWeek - 1)) { | |
| Logger.log(subId + ' - ' + rowWeek + ' - ' + 'New! '+ ' Row: ' + cellRow) | |
| const newCell = subsSheet.getRange('K' + cellRow) | |
| newCell.setValue(1) | |
| } else if (rowWeek !== lastWeek && !subs[subId].includes(rowWeek + 1)) { | |
| Logger.log(subId + ' - ' + rowWeek + '-' + 'Cancelled! '+ ' Row: ' + cellRow) | |
| const cancelledCell = subsSheet.getRange('L' + cellRow) | |
| cancelledCell.setValue(1) | |
| } | |
| }) | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment