Adding a formula to blank cells within a column

Question

 function copyformula(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formrange = ss.getRange("J1");
  var formval = formrange.getValues();
  var sourceSheets = ss.getSheets();
  for( sheetNumber = 2; sheetNumber < sourceSheets.length; sheetNumber++) {
  var range = ss.getRange("J3:J1000");
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == '') {
      var cell = range.offset(i, 0, 1, 1);   
      cell.setValues(formval);
      Utilities.sleep(750); //Edit value to adjust amount of pause between function calls (time is in milliseconds). Make it long enough to avoid #error, but not too long to save time.

    }
  }
} 
}

Hi so the aim is, to check for blank cells in column J3:J1000, (It will need to look through multiple sheets) If there is a blank cell, replace it with the formula in J1 (This will be on all sheets). I need it to copy the formula which is in J1 and paste it, so it changes all the cell references. Example: Lets say J307 is empty, the formula is copied from J1 and is pasted in J307, but all the If's etc are now based on J307 and other cells values around it. This will be set up to work with google triggers (on change)

Any help would be greatly appreciated :). At the moment the script above is not doing anything and times out.

Answer 1

• You want to put the formula from the cell "J1" on each sheet to the empty cells of the column "J".
• You want to reflect the row number to the formula when the formula is put to the cell.
• You want to check the sheet index from 2 to last index.
• You want to achieve this using Google Apps Script.

I could understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Sample script:

function copyformula(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheets = ss.getSheets();
  for( sheetNumber = 2; sheetNumber < sourceSheets.length; sheetNumber++) {
    var sheet = sourceSheets[sheetNumber];
    var values = sheet.getRange("J3:J" + sheet.getLastRow()).getValues();  // Modified
    var formula = sheet.getRange("J1");
    for (var i = 0; i < values.length; i++) {  // Modified
      if (!values[i][0]) {  // Modified
        formula.copyTo(sheet.getRange(`J${i + 3}`), SpreadsheetApp.CopyPasteType.PASTE_FORMULA);
      }
    }
  }
}

• In order to reflect the row number to the copied formula, I used copyTo.
• In this case, the row 3 to the last row of the data range are checked.
• If you don't want to use V8, please modify sheet.getRange(`J${i + 3}`) to sheet.getRange("J" + (i + 3)).

Reference:

• copyTo(destination, copyPasteType, transposed)