Is there a way to select range in Google scripts based on a variable?

Question

So I started out using google scripts and sheets after departing from excel.

In excel, when you want to select a particular range using a variable, the formula in the macro will be Range("C1:F" & lastrow).select where lastrow is the variable that you have previously named.

I cannot find an equivalent formula or script that can do the same in google scripting.

Basically all I want is to select a particular range to copy, but because the data row will vary, I want to select only the used rows rather than just the entire page.

Answer 1

Thank you so much for your help, it was critical in helping me to find the right direction!

I managed to cobble together a code that basically mirrors the excel code of selecting the rows and columns that i need. For some reason, I realised like google scripts are case sensitive but excel vb is not, so it is another thing that I did have to watch out for.

Basically, this is the equivalent of excel's macro for:

Sheets("MySheet").select
lastrow = Range("O99999").End(XLUp).row
Range("K1:V" & lastrow).select

Here is my code:

function zzz1() {
var sheet = SpreadsheetApp.getActive().getSheetByName('MySheet');
var lastrow = sheet.getRange("O"+(sheet.getLastRow()+1)).getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
var printrange = sheet.getRange("K1:V" + lastrow)
printrange.activate()
}

Answer 2

Using formulas

You can use the =INDIRECT() function, along with Google Sheets concatenation.

Returns a cell reference specified by a string.

As an example, given the following "Student info" sheet:

You can get the first three students by using =INDIRECT("Student info!A2:B"&B1) from another sheet in the same Spreadsheet, as seen below:

Using Google Apps Script

You can use the getRange(a1Notation) method of the Class Sheet, along with JavaScript concatenation to obtain a range of your choice. As an example:

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Student info');
  var lastRow = 4;

  var studentRange = sheet.getRange("A2:B" + lastRow);

  Logger.log(studentRange.getValues());
}

This will obtain the range A2:B4 and print its values using the Logger.

Alternatively, you can also use the getRange(row, column, numRows, numColumns) variation of the method. The usage, in this case, would be as follows:

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Student info');
  var lastRow = 4;

  var studentRange = sheet.getRange(2, 1, lastRow, 2);

  Logger.log(studentRange.getValues());
}

Extra

In order to import data from another Spreadsheet you can use the =IMPORTRANGE() function. For a setup such as the one above but obtaining the data from a different Spreadsheet, the function could be used as follows: