Copy formula to last row of column A (In Sheets )-CodePudding

I am very new to using macro's in google sheets, and struggling to replicate easy VBA codes

I am trying to find the last row of column A, then add formulas to various columns and copy down to the last row. In VBA it is as simple as **("B2:B" & Lastrow).select However, this is not so straight forward. I have found and manipulated the following, however it always goes to the very last row rather than the last row of data

var sheetName = "Week 1";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);

   spreadsheet.getRange('E7').activate()
  .setFormula('=iferror(if(match($B7,Names,0),VLOOKUP($B7,Special,3,false),0),0)');

  var lr = sheet.getLastRow();
  var fillDownRange = sheet.getRange(7, 5, lr-1);
  sheet.getRange('e7').copyTo(fillDownRange);

What needs to be done to the above so that it only fills down to the last row of data in column A

All and any help greatly appreciated

EDIT- I have update the code, (Obviously wrongly) and added to the s/sheet under tools, copy on tab

    var sheetName = "Week 1";
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName(sheetName);
    
        var lastRow = sheet.getLastRow();
      var range = sheet.getRange("A7"   lastRow);
      if (range.getValue() !== "") {
        return lastRow;
      } else {
        return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
      }
    
      var lr = getLastDataRow(sheet,"A")
    
    //Saturday Formula
       spreadsheet.getRange('E7').activate()
      .setFormula('=iferror(if(match($B7,Names,0),VLOOKUP($B7,Special,3,false),0),0)');
var fillDownRange = sheet.getRange(7, 5, lr-1);
  sheet.getRange('E7').copyTo(fillDownRange);

as between these two rows is a light bulb sayingabout unreachable code, and everything after and including Var = lr** is greyed out

}
    
      var lr = getLastDataRow(sheet,"A")

CodePudding user response：

Instead of var lr = sheet.getLastRow(); , try

lr = sheet.getRange('A1').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()

change A by E if needed.

edit : Change A1 by A7 si A4 and A6 are empty.

If you have discontinous value, prefer

function getLastDataRow(sheet,col) {
// col in letter
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(col   lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

and

lr = getLastDataRow(sheet,"A")

CodePudding user response：

Correction of your first code

function myFunction() {
  var sheetName = "Week 1";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  sheet.getRange('E7').setFormula('=iferror(if(match($B7,Names,0),VLOOKUP($B7,Special,3,false),0),0)');
  var lr= sheet.getRange('A7').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()
  var fillDownRange = sheet.getRange(7, 5, lr-6,1);
  sheet.getRange('E7').copyTo(fillDownRange);
}