CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
diggy
diggy

Reputation: 45

Setting getSheetbyName according to cell value

I'm not much of a programmer but managed to cobbled together a script and can't seem to get the last part working. I want to pass activelog string into log and feel like it's a simple syntax issue I'm overlooking: 

var activelog = e.source.getSheetByName("SHEET1").getRange("A1").getValue();
var log = e.source.getSheetByName(activelog);

Activelog gets the value of dropdown menu cell A1 on Sheet1 that contains list of all the relevant sheets. The script then copies down a formula in column C of whichever sheet is selected if something is typed in B1 of Sheet1. Right now specifying the sheetname "LOG" works fine:

function onEdit(e) {
    var activelog = e.source.getSheetByName("SHEET1").getRange("A1").getValue();
    var log = e.source.getSheetByName("LOG");
    var lr = log.getLastRow();
    var fdr = log.getRange(1,23,lr);
    var formula = "";

    if (e.source.getActiveSheet().getName() !== "SHEET1" || e.range.rowStart != 1 || e.range.columnStart != 2 ) return
    log.getRange("C1").setFormula(formula);log.getRange("C1").copyTo(fdr);
}

Upvotes: 0

Views: 1303

Answers (2)

random-parts
random-parts

Reputation: 2225

If the sheet is empty then var lr = log.getLastRow() will return a 0. Then when the log.getRange(1,23,lr) tries to getRange(1,23,0) it will error as it is an invalid range.

Add log.getLastRow() || 1; to set lr to 1, if the sheet is empty.

function onEdit(e) {
  var activelog = e.source.getSheetByName("SHEET1").getRange("A1").getValue();
  var log = e.source.getSheetByName(activelog);

  // If getLastRow() returns 0, set 1
  var lr = log.getLastRow() || 1;
  var fdr = log.getRange(1,23,lr);

  var formula = "=SUM(1+2)"; // for testing

  if (e.source.getActiveSheet().getName() !== "SHEET1" || e.range.rowStart != 1 || e.range.columnStart != 2 ) return
  log.getRange("C1").setFormula(formula);log.getRange("C1").copyTo(fdr);
}

Upvotes: 2

Liora Haydont
Liora Haydont

Reputation: 1283

You are missing a ; after your return in the last line. Right now if the range is incorrect and it enters your condition, it doesreturn log.getRange("C1").setFormula(formula);

And if it doesn't enter the if, all it does is log.getRange("C1").copyTo(fdr); without setting the formula first

Upvotes: 0

Related Questions