How can I possibly get the last row from a range of a sheet using apps script?-CodePudding

I have recently started a project that would automatically update datas of a stock card using google sheet and apps script and I wanted to know how can I get the last row from a range and write datas from it. For example,

this is the I wanted to update, how can I get the last row from range A14:G53 and automatically paste data from another sheet (database) using apps script.

I have tried coding and this is what I've got so far

function updateStocks(){
  var ss = SpreadsheetApp.getActive()
  var sh = ss.getSheetByName("database"); //sheet for my datas
  var range = sh.getRange("data") //range from "database sheet"
  var rData = range.getValues();
  rData.forEach((row, i) => {
  //sheetname from "database sheet"
  var sheetname = rData[i][3]; 
  var date = rData[i][9]; //date from "database sheet"
  var iQty = rData[i][4]; //issue qty from "database sheet"
  var iOffice = rData[i][10]; //issue office from "database sheet"
  var bQty = rData[i][13]; //balance qty from "database sheet"

  var data = {date,iQty,iOffice,bQty};
  //Paste values of each row to their respective sheet
  ss.getSheetByName(sheetname).appendRow(data);
  })
}

CodePudding user response：

To get the last row in a google sheet Spreadsheet is pretty straight forward, you could use app script or just put this in a cell = =Max(filter(row(A:A),A:A<>""))

A script can easily get it for the whole sheet.

function testLastRow() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var lastRow = ss.getActiveSheet().getLastRow();
}

From there, it's hard to tell if your array is setup correctly, but I would do something like this....

  var data = [[date,iQty,iOffice,bQty]]; //this might not be right... but get values.
  var zSheet = ss.getSheetByName(sheetname);
  var lastRow = zSheet.getLastRow();
  zSheet.getRange(lastRow 1,1,data .length,data [0].length).setValues(data);

CodePudding user response：

Try this:

=INDEX('Arrow_Stickers'!A:A,MATCH(143^143,'Arrow_Stickers'!A:A))

This will return the last used cell in column A. The 143^143 is just creating a super large number that will always be bigger than what you use in column A.

This answer comes from here:

References