Skip to content

Instantly share code, notes, and snippets.

@lordlycastle
Created June 7, 2022 11:34
Show Gist options
  • Save lordlycastle/b14a0a424256aedb9106da46d0acf3f4 to your computer and use it in GitHub Desktop.
Save lordlycastle/b14a0a424256aedb9106da46d0acf3f4 to your computer and use it in GitHub Desktop.
Can be used to help with determining if two ranges intersect and the intersection range.
const isEnabled = true
const numberOfShiftGroups = 2
function onEdit(event) {
console.log(`Event range: ${event.range.getA1Notation()}`)
if (!isEnabled && event.range.getSheet().getSheetName() !== "Shifts") {
return
}
const speadSheet = SpreadsheetApp.getActiveSpreadsheet()
const shiftsSheet = speadSheet.getSheetByName("Shifts")
const highlightingRange = shiftsSheet.getRange("A:B")
if (rangesIntersect(highlightingRange, event.range)) {
const overlapRange = {
row: event.range.getRow(),
col: event.range.getColumn(),
width: event.range.getLastRow() - event.range.getRow() + 1,
height: (event.range.getLastColumn() >= numberOfShiftGroups
? numberOfShiftGroups
: event.range.getLastColumn()) - (event.range.getColumn() - 1),
}
const highlightingSheet = speadSheet.getSheetByName("Shifts Highlighting")
const cellsFormatting = highlightingSheet.getRange(overlapRange.row, overlapRange.col, overlapRange.width, overlapRange.height,
)
console.info(`Formatting range: ${cellsFormatting.getA1Notation()}`)
const cellsToFormat = shiftsSheet.getRange(overlapRange.row, overlapRange.col, overlapRange.width, overlapRange.height)
cellsFormatting.copyFormatToRange(shiftsSheet.getSheetId(), overlapRange.col, overlapRange.row, overlapRange.height, overlapRange.width)
console.info(`Done for range: ${cellsToFormat.getA1Notation()}`)
}
}
/**
* Recycled from https://stackoverflow.com/a/67916559
*/
function rangesIntersect(r1, r2) {
if (r1.getSheet().getIndex() !== r2.getSheet().getIndex()) return false
if (r1.getLastRow() < r2.getRow()) return false
if (r2.getLastRow() < r1.getRow()) return false
if (r1.getLastColumn() < r2.getColumn()) return false
if (r2.getLastColumn() < r1.getColumn()) return false
return true
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment