CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Moses
Moses

Reputation: 236

Find last row and setvalue

I want to find the last row and put the data horizontally. Example if Row 2 is the last row Then I need to put data in A3,B3,C3,D,E3 If I run the below lines in AppsScript, I am not getting the expected result

function test_val(){ 
const ss= SpreadsheetApp.getActiveSpreadsheet()
const wsLocation = ss.getSheetByName("Location")     
wsLocation.getRange(wsLocation.getLastRow()+1,1).setValue("Apple1001");
wsLocation.getRange(wsLocation.getLastRow()+1,1).setValue("Banana1002");
wsLocation.getRange(wsLocation.getLastRow()+1,1).setValue("Carrot1004");
wsLocation.getRange(wsLocation.getLastRow()+1,1).setValue("Flowers1202");
wsLocation.getRange(wsLocation.getLastRow()+1,1).setValue("Sweets1022");
}

Upvotes: 0

Views: 557

Answers (1)

Cooper
Cooper

Reputation: 64032

Several ways to do the same thing:

function test_val() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("Sheet1")
  sh.appendRow(["Apple1001","Banana1002","Carrot1004","Flowers1202","Sweets1022"]);
}

you can also do this:

function test_val() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("Sheet1")
  //sh.appendRow(["Apple1001","Banana1002","Carrot1004","Flowers1202","Sweets1022"]);
  const r = [["Apple1001","Banana1002","Carrot1004","Flowers1202","Sweets1022"]];
  sh.getRange(sh.getLastRow()+1,1,r.length,r[0].length).setValues(r);
}

And this one is similar to what you were trying to do, except that it only calculates the sh.getLastRow() once because as you add more data to the spreadsheet the lastrow keeps on changing.

function test_val() {
  const ss = SpreadsheetApp.getActive()
  const sh = ss.getSheetByName("Sheet1")
  //sh.appendRow(["Apple1001","Banana1002","Carrot1004","Flowers1202","Sweets1022"]);
  const r = [["Apple1001","Banana1002","Carrot1004","Flowers1202","Sweets1022"]];
  //sh.getRange(sh.getLastRow()+1,1,r.length,r[0].length).setValues(r);
  let lr = sh.getLastRow();
  ["Apple1001","Banana1002","Carrot1004","Flowers1202","Sweets1022"].forEach((e,i)=>{
    sh.getRange(lr+1,i+1).setValue(e);
  });
}

All three do the same thing more or less the the first two are much faster and you should try to use getValues() and setValues() over getValue() and setValue() whenever you can as it will improve the overall performance of your code.

Check out Best Practices

Upvotes: 1

Related Questions