Retrieving the Last Row of a Sheet’s Range using Apps Script: Is it Possible?

Feedback


Solution 1:

Methodology

After reviewing the information you have provided in your post and replies, it appears that you should have the ability to address the problems by utilizing the following solutions:

  1. Utilize both

    getLastRow()

    and

    getLastColumn()

    to establish the extent of information within the

    Database

    section.
  2. Utilize

    map()

    for extracting the necessary information solely from the designated

    Database

    tab. As for the script, the data has been reorganized beforehand for proper placement within the

    Arrow_Stickers

    tab.
  3. To transfer the data to the designated tab, I utilized the value from the specified function and assigned it to the source and destination arrays. I ensured that both arrays have equal size to guarantee successful transfer.

The Script

The script provided below can serve as the foundation for developing your own script.

function transferData() {
  var ssDb = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Database');
  var ssAs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Arrow_Stickers');
  var lr = ssDb.getLastRow();
  var lc = ssDb.getLastColumn();
  var data = ssDb.getRange(4,1,lr-3,lc).getDisplayValues();
  var fData = data.map(data => {
    return [data[9], '', '', data[4], data[10], data[13]];
  })
  ssAs.getRange(14,1,lr-3,6).setValues(fData);
}

Kindly note that the script has been developed solely based on the sample sheets you provided, namely

Database

and

Arrow_Stickers

. Therefore, I recommend that you make necessary adjustments to the script.

Output

The script’s result should resemble this.

Output

References

  1. getLastRow()
  2. Array map()


Solution 2:


Obtaining the final row in a Google Sheet is a simple process that can be accomplished through App Script or by entering the following code into a cell:

=Max(filter(row(A:A),A:A<>""))

.

The entire sheet can be quickly obtained with a script.

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

It is difficult to determine if the array is correctly configured from that point. However, my suggestion would be to do the following…

  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);


Solution 3:

Try this:

Retrieve the value in column A of the ‘Arrow_Stickers’ sheet that corresponds to the position of 143 raised to the power of 143 in column A of the same sheet.

By utilizing the 143^143 formula, you can always obtain the final cell utilized in column A, as this creates an extremely large number that surpasses any value in column A.

The source of this answer can be found at https://spreadsheetpoint.com/get-last-value-in-column-google-sheets/.

In order to utilize the formula in your App Script, you might need to first place it in a cell within your Sheet and then refer to that cell in the App Script.

Frequently Asked Questions