Multi tool use

Move data to bottom of sheet

I've got a list of dates, clients, and shift data in a spreadsheet (rows A5:D34). I want to write this data to the last row of the sheet. Unfortunately, the number of rows is variable. On one week, the data may be 17 rows; later, it may be 23 rows.
I want to "archive" the data by moving the used rows to the bottom of the sheet.

I'm looking for a code (google script) solution that can work with the fact that the range to be copied is always 4 columns wide, but a variable number of rows.

Thanks for all your help!

getRange(start row, start column, number of rows to get, number of columns to get)

2 Answers
2

This is a quite common problem where we might not know or have to manually include the total number of rows in the sheet. We can use, getRange() with last row values or getDataRange() to extract the range.

getRange(start_row,start_column,number_of_rows,number_of_columns);


to use the last row in the above syntax starting from A1, simply change the parameters as below.

var range = getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());


The second option is to use getDataRange() which is

var range = sheet.getDataRange();


PS : Mind that range is a 2D array.

Hope this helps :)

I would suggest using sheet.getDataRange() to know how many rows and columns contain data. Once you know this you can derive the bottom row.

sheet.getDataRange()

To quickly go through the returned values, I would suggest using sheet.getDataRange().getValues() which will return a 2 dimensional array with all the values stored in your sheet.

sheet.getDataRange().getValues()

To know the last row containing data you can then easily do the following:

var lastRow = sheet.getDataRange.getValues().length;


Note that since the length of an array starts counting at 0 for the first row whereas when you want to set values back to your sheet your row count will start at 1. This means you need to add 1 to the lastRow count.

Now that you know the last row you can do some cleanup first to remove a previous bottom row with sheet.deleteRow(lastRow+1);

sheet.deleteRow(lastRow+1);

To then add a new bottom row you can simply use the append function where you pass an array with all the values for all cells of the row like this:

sheet.appendRow(["cellInColumnA";"cellInColumnB","cellInColumnC"]);


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.