1

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

1 Answer 1

1

Answer to the original question

I've created the following function:

function Merge() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName('Schedule');
    const range = s.getRange("B3:H20");

    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()) {
                cell.offset(0, 0, 7, 1).activate().mergeVertically()
            }
        }
    }
}

You can assign this script to your button.

In my example the range is B3:H20, but you might want to increase the range depending on the number of rows you want. The range you pick should not include the table headers or the column that has the times in it. The script assumes you don't have cells spanning 2 or more columns.

Once you've specified the range the script will check every cell in the range. If the cell is not empty it will try to merge the active cell with a number of cells below it.

Updated implementation after update original post

function Merge() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName('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()) {
                const cellValue = cell.getValue();
                if (
                    cellValue == 'test' ||
                    cellValue == 'test 2' ||
                    cellValue == 'test 3'
                ) {
                    cell.offset(0, 0, 2, 1).activate().mergeVertically();
                } else {
                    cell.offset(0, 0, 5, 1).activate().mergeVertically();
                }
            }
        }
    }
}

This updated implementation fixes some problems in the updated post.

The reference to a cell is not the same as the value of that cell. So if you want to create an if statement using the value of a cell you need to call the getValue method on that cell reference.

Other problems were just syntax errors. In Javascript an or operator is written like this: ||.

If there are some ranges or offsets you want to change, you are of course free to do so.

4
  • 1
    Absolutely phenomenal - working flawlessly. Huge thanks @Bas van der Linden Jul 9, 2020 at 0:15
  • Hey @Bas van der Linden - just wanted to give you an update here since you answered the original question; I realized I needed to update. My apologies for any confusion, but appreciate your great solution nonetheless. Jul 9, 2020 at 16:45
  • @AnthonyMadle I've updated my answer to address the problems of your updated question. If this addresses your problem, please accept my answer.
    – 5eb
    Jul 9, 2020 at 18:04
  • 1
    This is perfect. I had no idea about the I I - this community is so awesome. Learning something new daily. Jul 9, 2020 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.