Created
November 6, 2022 20:03
-
-
Save camilajenny/29e7366e98a3c6fe6a2bea1d96f063bd to your computer and use it in GitHub Desktop.
Google Spreedsheets script merging and setting border to week days
This file contains 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 numberToLetter(number){ | |
var temp = "" | |
var letter = ""; | |
while (number > 0){ | |
temp = (number - 1) % 26; | |
letter = String.fromCharCode(temp + 65) + letter; | |
number = (number - temp - 1) / 26; | |
} | |
return letter; | |
} | |
function getFirstMonday(colData) { | |
const MONDAY_LETTER = 'M'; | |
for(i = 0; i < colData.length; i++) { | |
if(colData[i] == MONDAY_LETTER) | |
return i+1; | |
} | |
return 1; // no Monday found | |
} | |
function mergeWeeks() { | |
var ss = SpreadsheetApp.getActiveSheet(); | |
// from Columns B to H (B = 2, NB = 366) | |
var loadingWeekDays = { | |
colStart: 2, | |
numColumns: 365, | |
row: 4, | |
numRows: 1 | |
}; | |
var mergeArr = []; // days that are starts of merge ranges | |
var colData = ss.getRange(loadingWeekDays.row, loadingWeekDays.colStart, loadingWeekDays.numRows, loadingWeekDays.numColumns).getValues().toString().split(","); | |
mergeArr.push(1); | |
var firstMonday = getFirstMonday(colData); | |
var currentDay = firstMonday; | |
if(currentDay != 1) | |
mergeArr.push(currentDay); | |
const DAYS_IN_WEEK = 7; | |
while(currentDay-1 < colData.length) { | |
currentDay += DAYS_IN_WEEK; | |
mergeArr.push(currentDay); | |
} | |
var weekNumber = 1; | |
var mergeRow = 3; | |
for (i = 1; i < mergeArr.length; i++){ | |
var mergeStart = mergeArr[i-1] + 1; | |
var mergeEnd = mergeArr[i]; // -1 + 1 = 0 | |
if (mergeEnd - mergeStart >= 1){ | |
// merge week cells | |
var weekRange = ss.getRange(numberToLetter(mergeStart) + mergeRow + ':' + numberToLetter(mergeEnd) + mergeRow); | |
weekRange.merge().setValue('WEEK ' + weekNumber); | |
// set borders | |
var borderRowRange = { | |
start: 3, | |
end: 100 | |
} | |
var weekRangeForWholeSpreadSheet = ss.getRange(numberToLetter(mergeStart) + borderRowRange.start + ':' + numberToLetter(mergeEnd) + borderRowRange.end); | |
weekRangeForWholeSpreadSheet.setBorder(null, true, null, true, null, null, 'blue', SpreadsheetApp.BorderStyle.DASHED); | |
} | |
weekNumber += 1; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment