Google Sheets Script to Fill Down intermittent blanks in a column

Question

Column C has an ID in it that pertains to several rows, but only the first row has an ID in it.

I need to copy that ID value to the blank cells beneath, until I hit a cell that has another value in it.

Picture of the spreadsheet

I have tried adapting this script but it hits a timeout error.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var last = sheet.getLastRow();//how many times should I do this?

  for (i = 5528; i < last; i++) { 
    var test = sheet.getRange(i, 1,1,1);
    //Logger.log(test);
  //looks to see if the cell is empty
    if (test.isBlank()) {
      var rewind = sheet.getRange(i-1, 1, 1, 1).getValues();//gets values from the row above
      sheet.getRange(i, 1, 1, 1).setValues(rewind);//sets the current range to the row above
    } 

  }
}

i is set to a big number because every time it times out I have to start over!

I have read that it would be better to bring in the column in an array, work on it, then put it back out to save a lot of time.

I have tried to adapt this but can't get past the variable.

Am I on the right track? I would like to pretty up a solution for the future where I can pass a column or range and do the same thing.

Here is my failing attempt:

function FillDown2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");
  var tracts = sheet.getRange("C15:C").getValues();
  var allTractList = [];
  var title;

  for (var row = 0, var len = tracts.length; row < len; row++) {
    if (tracts[row][0] != '') {
      //response = UrlFetchApp.fetch(tracts[row]);
      //doc = Xml.parse(response.getContentText(),true);
      title = tracts[row][0];
      //newValues.push([title]);  
      allTractList.push([title]);  
      Logger.log(title);
    } else allTractList.push([title]);
  }

  //Logger.log('newValues ' + newValues);
  Logger.log('allTractList ' + allTractList);

  // SET NEW COLUMN VALUES ALL AT ONCE!
  sheet.getRange("B15").offset(0, 0, allTractList.length).setValues(allTractList);
  return allTractList; 
}

I'm glad that you find a solution. Your question could be better if you include the exact wording of the error message. Reference [mcve]. — Rubén, Nov 19 '16 at 14:33

score 0 · Accepted Answer · edited May 23 '17 at 12:24

Holy Smokes! I did it!

Not sure about why error happened but I had made some changes and got it to work!

Hope this is helpful to others:

function FillDown2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet2");
  var tracts = sheet.getRange("C15:C").getValues();
  var allTractList = [];
  var title;
  var len = tracts.length;

  for (var row = 0; row < len; row++) {
    if (tracts[row] != '') {
      //response = UrlFetchApp.fetch(tracts[row]);
      //doc = Xml.parse(response.getContentText(),true);
      title = tracts[row];
      //newValues.push([title]);  
      allTractList.push([title]);  
      Logger.log(title);
    } else allTractList.push([title]);
  }

  //Logger.log('newValues ' + newValues);
  Logger.log('allTractList ' + allTractList);

  // SET NEW COLUMN VALUES ALL AT ONCE!
  sheet.getRange("B15").offset(0, 0, allTractList.length).setValues(allTractList);
  return allTractList; 
}

Credit to Bryan here:

Google Sheets Script to Fill Down intermittent blanks in a column

1 Answers1