AutoFill Data with Blank Rows - Google Sheets / Google Apps Script

0
votes

I have the below spreadsheet that I would like to AutoFill the persons name. the issue is that there are blank rows between the names. Each name is in line with a sku2 and needs to be inline with all locations. there can be up to 10 blank rows (due to how many locations).

if I could loop this maybe

function LoopTillLr() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A2').activate();
  spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
};

Appreciate any help

enter image description here

2
google-apps-scriptgoogle-sheets
Any specific reason you are wanting to do this using scripts? Because this can be done very easily using IF formula. - Broly
Could you please share a sample of the desired outcome? - Iamblichus

2 Answers

1
votes

If you only want to replicate the NAME values against variable LOCATION values then use this script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSheet();
  var lastRow = ss.getDataRange().getLastRow();

  for (var i = 1; i < lastRow+1; i++) {
    if (ss.getRange(i,1).getValue() == "") {
      var value = ss.getRange(i-1,1).getValue();
      ss.getRange(i,1).setValue(value);
    }
  }
}

Ensure that A2 is not empty else the script will fail.

1
votes

If it is a lot of records, you can create a function and run it. The following does this until the end of the sheet, so make sure to delete all the rows towards the end which you do not need or adjust the range in the 2nd row.

function autoFillDown(){
  const range = SpreadsheetApp.getActiveSheet().getRange("A:A");
  const rows = range.getValues();
  let outputArray = [];

  rows.forEach( row => {
    // if it contains a name, leave it
    if( row[0].length > 1){ 
      outputArray.push( [row[0]] )       
    // otherwise replace it with the value above it
    } else {
      outputArray.push( [outputArray[outputArray.length-1]] );
    }    
  });  
  range.setValues( outputArray );
}