Skip to content

Instantly share code, notes, and snippets.

@banan314
Created November 6, 2022 20:03
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save banan314/29e7366e98a3c6fe6a2bea1d96f063bd to your computer and use it in GitHub Desktop.
Save banan314/29e7366e98a3c6fe6a2bea1d96f063bd to your computer and use it in GitHub Desktop.
Google Spreedsheets script merging and setting border to week days
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