Google表格列数 [英] Google Sheets Number of columns

问题描述

我想读一张表，我想知道它的尺寸（列数，行数）。

I want to read a sheet and i want to know it's dimensions (number of columns, number of rows).

获取这些信息的最简单方法是什么？ From

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.

这是可能的吗？

推荐答案

当您说不需要表单的实际大小时，因为可能会有很多空单元格。 不确定是否意味着你只是想要数据的最大数据范围最后一行和上一个col >？或只是数据行和列的数量

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

如果最后一行/列数据范围，您可以使用 spreadsheets.values.get（）并找到长度值然后是包含数据的最后一列列表中最大的 array.length 。
这是可行的，因为返回值排除多余的空行和列：



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.

使用google apps script& javascript可以在谷歌应用程序脚本中测试
[希望你可以了解这个过程 - 有一段时间没有用过Java，并且不希望它成为一个混乱点]

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

编辑：获取列的简单方法将在 Sheets.Spreadsheets.Values.get（ID，SN，{MajorDimension：COLUMNS}）中添加 majorDimension 然后 arr.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

从那里你可以过滤掉在包含数据的空列或行之间 - 获得仅数据行/列的数量;据我所知，无法从API本身获取该数据。

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.

这篇关于Google表格列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！