I want to read a sheet and i want to know it's dimensions (number of columns, number of rows).
What is the easiest way to obtain these? From
Get get = Sheets.getSheetsService().spreadsheets().values().get(spreadsheetId, sheetRange);
I want to know the number of columns, that contain data, I don't want the actual size of the sheet, since there might be lots of empty cells.
Is this possible in any way?
When you say you "don't want the actual size of the sheet, since there might be lots of empty cells." Not sure if you mean you just want the max data range, last row
and last col
with data? or just the count of rows and columns with data
If last row/col data range, you can use spreadsheets.values.get() and find the length
of the returned values for rows and then the largest array.length
in the list for the last column
with data.
This works because returned values exclude extra empty rows and cols:
For output, empty trailing rows and columns will not be included.
Example using google apps script & javascript that can be tested in google apps script
[hoping you can get an idea of the process - haven't used Java in awhile and didnt want it to be a point of confusion]
function getDimensions () {
var data = Sheets.Spreadsheets.Values.get("SPREADSHEET_ID","SHEET_NAME");
// returned value list[][]
var arr = data.values;
// last row with data
var rows = arr.length;
// last column with data
var cols = arr.reduce(function(accumulator, el, i) {
if (i == 1) { accumulator = accumulator.length }
return Math.max(accumulator, el.length);
});
}
edit: An easy way to get the columns would be to add majorDimension
parameter in Sheets.Spreadsheets.Values.get("ID","SN", {majorDimension: "COLUMNS"})
then the arr.length
returns the column length
From there you could filter out the empty columns or rows that are between the ones which contain data - to get a count of only data rows/cols; as far as I am aware there is no way to get that count from the API itself.