Goal:

I'm trying to:

1. Have a google app script (not a formula) review a column, and if there is a blank in the column, copy down the value above the blank until the next cell that isn't blank, until the column has all data fields filled in (i.e. iterate through the whole column).

As always appreciate the help in advance!

Issue/Troubleshooting:

1. I've attempted to review stackoverflow for users asking similar questions, but it usually involves the autofill method which I don't believe works in this case due trying to edit this in the same column, and it's text. Or the questions involve copying formulas down which I'm not trying to do in this case.

2. I've attempted to try and use different for loops, and interact with arrays to pull the values if they're not blank to the previous blank, but I think I'm not accessing the array correctly and/or using the loop correctly.

3. I'm not encountering any coding errors, the scripts executes but nothing changes in the sheet, so most likely it's my code that isn't working

Current example of sheet:

Desired Outcome:

see column D for change

Raw Data:

Code so far:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

  var colToReview = ss.getRange(2,4,ss.getLastRow()) //the column to review data and then fill in
  var ownerVals = colToReview.getValues();//get all the values for an array?



  for (i=0;i<ownerVals.length;i  ){

    if(ownerVals==''){
      ss.getRange(i 1,4,ss.getLastRow()).setValues(ownerVals[i-1])
    }//end of IF
  }//end for FOR
}

Link(s) to similar questions:

1. AutoFill Data with Blank Rows - Google Sheets / Google Apps Script
2. AutoFill Formula (running across spreadsheet) - Google Sheets / Apps Script

CodePudding user response：

Try this:

function myfunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let l = '';
  let o = [];
  vs.forEach((r, i) => {
    if (i > 0) {
      if (r[3] == '') {
        o.push([l])
      } else {
        l = r[3];
        o.push([r[3]]);
      }
    } else {
      l = r[3];
    }
  });
  sh.getRange(3, 4, o.length,o[0].length).setValues(o);
}

Doing it with a map.

function myfunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  let vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let l = '';
  let o = vs.map((r, i) => {
    if (i > 0) {
      if (r[3] == '') {
        return [l];
      } else {
        l = r[3];
        return [r[3]];
      }
    } else {
      l = r[3];
      return [r[3]];
    }
  });
  sh.getRange(2, 4, o.length,o[0].length).setValues(o);
}