Do you know if there is some call in Google APIs which is roughly equal to MS Excel VBAs Worksheet.UsedRange? Attempted finding something, but have not had any positive results so far ...
Short explanation what Worksheet.UsedRange does and what I expect to have in Google APIs:
Sub sub1()
Worksheets("List1").Activate
ActiveSheet.UsedRange.Select
End Sub
You want to return the "used" range. As @TheMaster has pointed out, there is no method that will do this. However, it is possible to get the range by looping through rows and columns.
Try this script:
function usedRange() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName("so_79510837")
var range = sheet.getDataRange()
var values = range.getValues()
var numRows = values.length
var numCols = values[0].length
// Logger.log("DEBUG: number of Rows = "+numRows+" and number of Columns = "+numCols)
// loop though rows to find the first row with data
for (var r=0;r<values.length;r++){
if (values[r].filter(String) >1){
var rowNumber = r+1
Logger.log("DEBUG: row number "+rowNumber+" is the first row to contain data")
break
}
}
// loop though columns to find the first column with data
for (var c=0;c<values[0].length;c++){
// get each column as a 1D array
var columnData = values.map(function(e){return e[c];});
if (columnData.filter(String) >1){
var colNumber = c+1
Logger.log("DEBUG: Column number "+colNumber+" is the first column to contain data")
break
}
}
// Logger.log("DEBUG: last row = "+sheet.getLastRow()+", last column = "+sheet.getLastColumn())
var usedRange = sheet.getRange(rowNumber,colNumber,sheet.getLastRow()-rowNumber+1,sheet.getLastColumn()-colNumber+1)
Logger.log("DEBUG: the used range = "+usedRange.getA1Notation())
}
Sample output
DEBUG: the used range = D6:J18
Sample data
| A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|
| 333 | ||||||||||
| 111 | ||||||||||
| 222 | ||||||||||
The closest is Sheet.getDataRange(). However, the bounding box always starts from A1. So, you get two desired borders on the right and down, but not on the left and up. However, if you still need all the borders, you might be able to write something custom using getValues() and looping through to remove all the empty cells on the left and up.
You may consider this another possible solution to your issue.
If it is explicitly meant for the Sheets API, then the Google Sheets API does not have a built-in feature that is equivalent to UsedRange. If you'd like to request a feature similar to Excel's Worksheet.UsedRange in Google Sheets, I suggest submitting a feature request through Google’s Issue Tracker. Clearly explain your use case and the benefits of this feature to increase the likelihood of it being considered.
Refer to this documentation for instructions on creating an issue in the Google Issue Tracker.
