1
\$\begingroup\$

I am working with a spreadsheet containing a bunch of 'sections', e.g where the 'URL' column is set to 'google.com/search' in A1, and left empty while a bunch of things related to that URL are done in other columns (let's say we extract all of the

tags, and list them). As this all applies to the same URL, there's no need to repeat it, so the URL column is left blank until A7, when the URL changes to 'google.com/about'.

I wanted to merge these cells together with the cell containing the URL instead of leaving them empty, but I found myself repeating the same operation a bunch of times:

  • Find a cell with text in it
  • Drag down until the next cell with text in it
  • Merge vertically
  • Repeat.

Therefore, I set out to make a macro. For reference, this is my very first experience with Google Apps Script, so while I probably could have done things a lot simpler (please do tell me if so!), I am glad it turned out so long because I learned a lot.

Anyway, here is my code. You simply select a range, and it will apply the aforementioned process to all columns.

Disclaimer: All code is my own, except the columnToLetter function, which I stole borrowed from AdamL's answer on this question

function mergeAtText() {
  var spreadsheet = SpreadsheetApp.getActive();
  var range = spreadsheet.getActiveRange().getA1Notation();
  var toMerge = [];

  var toCheck = [];
  var axis = range.split(":");

  var cols = [];
  var rows = [];

  // Extract all of the selected rows and columns separately
  // This gives us a list of rows (e.g [11, 14, 19, 25]) that
  // the selected range notation references (e.g ["A11:B14", "C19:G25"]
  // would result in [11, 14, 19, 25]).

  // We also do the same with columns, i.e to get ["A", "B", "C", "G"]
  for (var i=0;i<axis.length;i++){
    var cell = axis[i]
    var row = /(\d+)/.exec(cell);
    var col = /[a-zA-Z]+/.exec(cell);

    if (rows.indexOf(row[0]) < 0){
      rows.push(row[0]);
    }
    if (cols.indexOf(col[0]) < 0){
      cols.push(col[0]);
    }
  }

  //Sort the rows
  rows = rows.sort();

  var rowList = [];
  // Loop through from the lowest row number to highest, i.e
  // get everything in between: if we have [11, 15], we'd get [11, 12, 13, 14, 15]
  for (var i=parseInt(rows[0]);i<parseInt(rows[rows.length-1])+1;i++){
    rowList.push(Math.floor(i).toString());
  }
  rows = rowList;


  // Define a helper function...
  function getColNum(colName){
    return spreadsheet.getRange(colName + ":" + colName).getColumn()-1;
  }

  function columnToLetter(column){
    var temp, letter = '';
    while (column > 0){
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  }

  // ... to do the same with columns.
  cols = cols.sort();
  var colList = [];
  for (var i=getColNum(cols[0]);i<getColNum(cols[cols.length-1])+1;i++){
    colList.push(columnToLetter(Math.floor(i)+1));
  }
  cols = colList;


  // Now that we have the rows and columns, combine to get all
  // of the selected cells in the range individually.
  var checker = [];
  for (var i=0;i<cols.length;i++){
    var toPush = [];
    for (var r=0;r<rows.length;r++){
      if (checker.indexOf(cols[i]+rows[r]) < 0){
        checker.push(cols[i]+rows[r]);
        toPush.push(cols[i]+rows[r]);
      }
    }
    toCheck.push(toPush);
  }

  // For each selected cell...
  for (i=0;i<toCheck.length;i++){
    var col = toCheck[i];
    var inMerge = false;

    for (b=0;b<col.length;b++){
      var val = spreadsheet.getRange(col[b]).getValue(); // Check if there is some text in it
      if (val){ // If there is text
        if (inMerge){
          toMerge.push([inMerge, col[b-1]]); // Finish any merge ranges we are currently in
        }
        inMerge = col[b]; // and mark the start of a new merge range
      }
    }

    if (inMerge){
      toMerge.push([inMerge, col[col.length-1]]); // At the end, finish off the current merge range
    }
  }

  for (i=0;i<toMerge.length;i++){
    spreadsheet.getRange(toMerge[i][0] + ":" + toMerge[i][1]).mergeVertically(); // Simply go through each range, and merge them.
  }
};

Any comments on code quality, conciseness, etc. are appreciated. Also, any suggestions on how to update the population code to work with multiple selection ranges would be appreciated.

Here's a GIF giving an example of the usage (apologies about low resolution):

GIF

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.