I'm building out a scheduling assistant; the look is great, but it is very tedious to Merge & highlight each schedule block individually, so naturally I turned to Apps Script.
I'm entering different Names (text values) on the schedule that may or may not be different every time. The catch is that, if the text = "test 1", "test 2" or "test 3", I'm looking to merge that cell with ONE cell below; however, if there is any other text value, I want to merge that cell with four cells below.
My goal is to use a "Transform" button to run a script that would take any cell that has a text value on the schedule, evaluate it, and merge & center that text value with the correct # of cells.
My plan is to use Conditional Formatting to highlight these blocks after.
Thanks to @Bas van der Linden, I had a script that was working perfectly before I realized I needed to edit. Logically, I want my code to read like this:
If the cell is blank, do nothing;
If the cell value = "test 1", "test 2", or "test 3", merge vertically with a single cell below;
If the cell value = any other word, merge vertically with 4 cells below it;
I'm very confused with the cell variable, I thought this would work. This is my new attempt:
function Merge2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Create Schedule');
const range = s.getRange("W4:AC22");
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)
if (!cell.isBlank()){
if(cell == 'test' or 'test 2' or 'test 3')
cell.offset(0,0,2,1).activate().mergeVertically(){
if(cell <= ""
cell.offset(0, 0, 5, 1).activate().mergeVertically(){
}
}
}
I've also linked a sample sheet, because I'm sure this sounds very confusing without seeing what I've done thus far:
https://docs.google.com/spreadsheets/d/1KSYihfSaqDxl58J6CLubEsFFeAhltq__CV__wXT9whU/edit#gid=0