CODEWITHSUNDEEP
google-sheetsgoogle-sheets-macros
Stephen Lloyd
Stephen Lloyd

Reputation: 805

Google Sheets - Unmerge cells and fill down

I need to take a sheet maintained by someone else and do the following (so that I can export to a csv):

It's too much to do it by hand, and it will need done periodically. My javascript and google sheets object model knowledge approximate zero, but I know it's possible because I could do it in VBA. I searched but can only find programmatic answers for VBA/Excel.

How can I do this efficiently in Google Sheets?

Upvotes: 1

Views: 2914

Answers (2)

SJGD
SJGD

Reputation: 138

Adapted from @lreeder's answer

The following breaks and fill blank with above on the selected range:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('BreakAndFill')
      .addItem('Break Fill Blank Cells', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  BreakandfillBlankWithAbove()
}

//Breaks range
//Iterates over the range from top to bottom
//and left to right, and fills blank cells 
//with the value right above them.
function BreakandfillBlankWithAbove() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  Logger.log('Range height is: ' + range.getHeight());
  var values = range.getValues();
  range.breakApart();
  var width = values[0].length;
  var height = values.length;
  Logger.log('Width is ' + width);
  Logger.log('Height is ' + height);
  for (var i = 0; i < width; i++) {
    var lastVal = '';
    for(var j = 0; j < height; j++) {   
      var currValue = values[j][i];
      var dataType = typeof(currValue);
      //Empty string check returns true if dataType
      //is a number, and value is zero.  In that case
      //we don't want to overwrite the zero, so only
      //check emptyString for string types.
      if(currValue === undefined || 
         (dataType === 'string' && currValue == '')
        )  {
        //getCell parameters are relative to the current range
        //and ones based
         var cell = range.getCell(j+1, i+1);
         cell.setValue(lastVal);
      }
      else {
        lastVal = currValue;
      }
    }
  }
  SpreadsheetApp.flush();
}

Upvotes: 0

Broly
Broly

Reputation: 921

You can use the breakapart() class to do this. I am assuming that the merged range is not static and has multiple occurrences. This script will unmerge all merged ranges in the active sheet.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).breakApart();
}

Upvotes: 1

Related Questions