Fill down - Google Apps Script for Spreadsheets

Question

Is there a function that can fill down data to all empty cells from the row above?

If not, could anyone help me out with a Google Apps Script (or other solution) that can go through every row in my google spreadsheet, check for empty cells, and if found, fill them in with data from the row above?

Such a function should be already included in a spreadsheet program I think (and "fill up" as well).

Thank you!

Answer 1

Such a function should be already included in a spreadsheet program I think (and "fill up" as well).

I'll attempt to describe the native functionality in GSheets pertaining to this.

1. If you double-click the little blue square on the bottom-right of a selected cell or range, this will automatically fill that cell down to the end of a block of populated cells on the left. Alternatively, you can just drag this little blue box (down, up, left or right) to auto-fill.

2. You can select the source cell and cells underneath it (refer to point 4), and then press Ctrl+Enter, to fill down. You can also use Ctrl+Enter or Ctrl+R to fill right.

3. You can select the source cell, press Ctrl+C to copy, then select cells above (or to the left - and again refer to point 4), and then press Ctrl+V to paste. This has the effect of filling up (or left).

4. You can quickly select blank cells under (or over, or right of, or left of) the source cell by pressing Ctrl+Shift+Down (or Up, or Right, or Left). This will take you to either the limit of the sheet, or the next populated cell. If the latter, you can then press Shift+Up (or Down, or Left, or Right) to back-track to the last blank cell. And then fill as required as per points 2 and 3.

5. You will need to do this for each non-contiguous group of blank cells. If you want to be able to quickly auto-fill non-contiguous groups of blank cells, then yes, a script would be required.

Answer 2

Here's a working script that adds a custom menu to your Google Sheet. Load the script for a Google Sheet, select a range on the sheet, and select "Fill Blank Cells" from the custom menu. Blank cells will be filled with the value of the cell above.

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

function menuItem1() {
  fillBlankWithAbove()
}

//Iterates over the range from top to bottom
//and left to right, and fills blank cells 
//with the value right above them.
function fillBlankWithAbove() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  Logger.log('Range height is: ' + range.getHeight());
  var values = range.getValues();

  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();
}

Answer 3

Use copyTo. I'll mostly adapt the answer I found about this in here:

  var sheet = SpreadsheetApp.getActiveSheet();
  var originRange = sheet.getRange("A1:B1");
  var target = sheet.getRange("A2:B");
  originRange.copyTo(target);

This will auto-fill / expand the formulas in originRange to everything below, as with copy & paste.

Answer 4

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name");
var formulas = sheet.getRange(10, 1, 1, 10).getFormulasR1C1();
var newRange = sheet.getRange(11, 1, 1, 10);
newRange.setFormulasR1C1(formulas);

Using getFormulasR1C1() and then setFormulasR1C1(formulas) This function imitates mouse dragging of the formula

Please note that if the cell doesn't contain formula it will show error. You will need to work around this problem by emptying the cells.