如何获取Sheet的数据范围 [英] How to get a data range of the Sheet

问题描述

我需要获取工作表的数据范围.我必须调用 API 两次.

I need to get a data range of the Sheet. I must call APIs twice.

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/Sheet1?majorDimension=ROWS
// Height is result.length

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/Sheet1?majorDimension=COLUMNS
// Width is result.length

但是我不需要数据，我只需要数据范围的大小.有没有办法得到它?

But I do not need data, I only need the size of the data range. Is there a way to get that?

推荐答案

很遗憾，我找不到使用 Sheets API 直接检索数据范围的大小"的方法.因此，作为一种解决方法，我建议使用 Web Apps.在您的情况下，您似乎已经拥有访问令牌.所以这个提议使用访问令牌访问 Web 应用程序.本提案流程如下.

Unfortunately, I couldn't find the method for directly retrieving "the size of the data range" using Sheets APIs. So as a workaround, I would like to propose to use Web Apps. In your case, it seems that you already have an access token. So this proposal accesses to Web Apps using the access token. The flow of this proposal is as follows.

function doGet(e) {
  var id = e.parameter.id;
  var sheetName = e.parameter.sheetname;
  var result = {};
  try {
    var ss = SpreadsheetApp.openById(id).getSheetByName(sheetName);
    result.LastRow = ss.getLastRow();
    result.LastColumn = ss.getLastColumn();
  } catch(er) {
    result.Error = er;
  }
  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

复制粘贴后，请直接运行doGet().这样就可以进行授权了.请忽略脚本错误.

After copied and pasted this, please directly run doGet(). By this, the authorization can be done. Please ignore the error of script.

部署Web Apps的条件如下.

The condition for deploying Web Apps is as follows.

• 使用 doGet() 在项目的脚本编辑器上.
  • 发布 -> 部署为网络应用
  • 对于将应用程序执行为:"，设置我".
  • 对于谁有权访问应用程序:"，设置仅限我自己".
  • 点击部署.
  • 复制 Web 应用程序的 URL.
  • On script editor on the project with doGet().
    • Publish -> Deploy as web app
    • For "Execute the app as:", set "Me".
    • For "Who has access to the app:", set "Only myself".
    • Click Deploy.
    • Copy URL of Web Apps.

    在上述条件下，您只能在使用访问令牌时访问 Web 应用程序.未使用访问令牌时，返回Unauthorized 错误.在这种情况下，请将 https://www.googleapis.com/auth/drive 添加到范围中.

    At above condition, you can access to Web Apps only when the access token is used. When the access token is not used, the error of Unauthorized returns. In this case, please include https://www.googleapis.com/auth/drive to the scopes.

    这是一个示例卷曲.使用它，您可以检索电子表格的 LastRow 和 LastColumn.使用时，请将 URL 替换为您的 Web Apps 的 URL，并输入电子表格 ID 和工作表名称.

    This is a sample curl. Using this, you can retrieve LastRow and LastColumn of the spreadsheet. When you use this, please replace URL to your Web Apps's URL, and input spreadsheet ID and sheet name.

    curl -GL 
      -H "Authorization: Bearer ### your access token ###" 
      -d "id=### spreadsheet ID ###" 
      -d "sheetname=### sheet name ###" 
      "https://script.google.com/macros/s/#####/exec"
    

    示例结果:

    {"LastRow":10,"LastColumn":10}
    

    如果这对您没有用，我很抱歉.

    If this was not useful for you, I'm sorry.

    这篇关于如何获取Sheet的数据范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！