链接到已发布的 Google 表格中的特定表格 [英] Link to a specific sheet in published Google Sheet

问题描述

我看到类似的问题以前被问过多次，但我似乎无法让它们起作用.我还了解到 Google 更改了其网址的构建方式，因此不幸的是，大多数解决方案都已弃用.

I see similar questionns has been asked multiple times before, but I cant seem to get them to work. I've also read that Google changed how their URLs are built up, so most of the solutions were deprecated unfortunately.

我正在寻找指向已发布工作簿特定工作表的链接.我制作了一个简单的工作簿进行测试，发布的链接如下所示:nodocrels.google.com/docrels.d/e/2PACX-1vRrmEbjecLvXhbm409pa6JJXZd_ZXTG8Zt6OevIUs5Axq5oxlCZKU0QXk-2lW05HyXJ2B4Bzy3bG-4L/pubhtml

I'm looking for a link to a specific sheet of a workbook that has been published. I've made a simple workbook to test, and the published link looks like this: https://docs.google.com/spreadsheets/d/e/2PACX-1vRrmEbjecLvXhbm409pa6JJXZd_ZXTG8Zt6OevIUs5Axq5oxlCZKU0QXk-2lW05HyXJ2B4Bzy3bG-4L/pubhtml

如您所见，有一个顶部菜单可以在工作表之间进行更改，但这不会影响 URL.

As you can see there is a top menu to change between the sheets, but that doesn't affect the URL.

有什么办法可以直接获取Sheet2"的 URL?或者这取决于拥有工作表 ID(我不是上述电子表格的所有者)?

Is there any way I can get a URL to "Sheet2" directly? Or is that dependant on having the Sheet ID (I'm not the owner of said spreadsheet)?

推荐答案

我相信你的目标如下.

• 您想要从 https://docs.google.com/spreadsheets/d/e/2PACX-1vRrmEbjecLvXhbm409pa6JJXZd_ZXTG8Zt6OevIUs5Axq5oxlCZKU0lWhtml4B2G-1000 的 URL 中检索 Sheet2 中的值.
• 此电子表格的所有者不是您.
• 您不知道电子表格 ID 和电子表格中的每个工作表 ID.您只知道 https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml 的网址.
• 在上述情况下，您要检索工作表 2 的直接 URL.

对于上述目标，这个答案怎么样?

For above goal, how about this answer?

遗憾的是，现阶段似乎无法直接从https://docs.google.com/spreadsheets/d/e/2PACX-#的URL中获取Spreadsheet ID和每个sheet ID##/pubhtml.我认为这是当前的规范.另外我认为这个原因可能是由于安全性.因此，为了实现您的目标，需要考虑解决方法.

Unfortunately, in the current stage, it seems that the Spreadsheet ID and each sheet ID cannot be directly retrieved from the URL of https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml. I think that this is the current specification. Also I think that this reason might be due to the security. So in order to achieve your goal, it is required to think of the workaround.

在这个答案中，作为一种解决方法，我想使用由 Google Apps Script 创建的 Web 应用程序来实现您的目标.使用Web Apps时，可以检索Sheet2的直接链接.

In this answer, as a workaround, I would like to achieve your goal using Web Apps created by Google Apps Script. When Web Apps is used, the directlink of Sheet2 can be retrieved.

此解决方法的流程如下.

The flow of this workaround is as follows.

1. 从 https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml 的 URL 下载 XLSX 数据形式的 Google 电子表格.
2. 将 XLSX 数据转换为 Google 电子表格.
3. 将转换后的 Google 电子表格发布到 Web.
4. 检索每个工作表的 URL.

1. Download the Google Spreadsheet as a XLSX data from the URL of https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
2. Convert the XLSX data to Google Spreadsheet.
3. Publish the converted Google Spreadsheet to Web.
4. Retrieve the URLs of each sheet.

用法:

请执行以下流程.

Usage:

Please do the following flow.

Web Apps 的示例脚本是 Google Apps 脚本.所以请创建一个 Google Apps Script 项目.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

如果您想直接创建它，请访问https://script.new/.在这种情况下，如果您未登录 Google，则会打开登录屏幕.所以请登录谷歌.这样，Google Apps Script 的脚本编辑器就打开了.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

请将以下脚本(Google Apps 脚本)复制并粘贴到脚本编辑器中.并且请在高级 Google 服务中启用 Google Drive API.此脚本适用于 Web 应用程序.

Please copy and paste the following script (Google Apps Script) to the script editor. And please enable Google Drive API at Advanced Google services. This script is for the Web Apps.

function doGet(e) {
  const prop = PropertiesService.getScriptProperties();
  const ssId = prop.getProperty("ssId");
  if (ssId) {
    DriveApp.getFileById(ssId).setTrashed(true);
    prop.deleteProperty("ssId");
  }
  const inputUrl = e.parameter.url;
  const re = new RegExp("(https?:\/\/docs\.google\.com\/spreadsheets\/d\/e\/2PACX-.+?\/)");
  if (!re.test(inputUrl)) return ContentService.createTextOutput("Wrong URL.");
  const url = `${inputUrl.match(re)[1]}pub?output=xlsx`;
  const blob = UrlFetchApp.fetch(url).getBlob();
  const id = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "temp"}, blob).id;
  prop.setProperty("ssId", id);
  Drive.Revisions.update({published: true, publishedOutsideDomain: true, publishAuto: true}, id, 1);
  const sheets = SpreadsheetApp.openById(id).getSheets();
  const pubUrls = sheets.map(s => ({[s.getSheetName()]: `https://docs.google.com/spreadsheets/d/${id}/pubhtml?gid=${s.getSheetId()}`}));
  return ContentService.createTextOutput(JSON.stringify(pubUrls)).setMimeType(ContentService.MimeType.JSON);
}

• 在本例中，使用 GET 方法.
• 在此脚本中，当运行以下 curl 命令时，Google 电子表格会下载为 XLSX 数据，并将 XLSX 数据转换为 Google 电子表格.然后，将转换后的电子表格发布到 Web.通过这种方式，可以检索每个工作表的直接链接.
  • 此外，在此脚本中，它假设原始电子表格已更改.因此，如果您再次运行 curl 命令，现有电子表格将被删除，并通过从原始电子表格下载来创建新电子表格.在这种情况下，网址会更新.
  • 因此，如果电子表格未更改，您可以继续使用检索到的 URL.当然，您也可以直接使用下载转换后的电子表格.
  1. 在脚本编辑器上，通过发布"->部署为 Web 应用程序"打开一个对话框.
  2. 为将应用程序执行为:"选择我".
     • 由此，脚本以所有者身份运行.
  • 在这种情况下，不需要请求访问令牌.我认为我推荐使用此设置来实现您的目标.
  • 当然，您也可以使用访问令牌.届时，请将其设置为任何人".
  1. 点击查看权限".
  2. 选择自己的帐户.
  3. 在此应用未经验证"处点击高级".
  4. 点击转到###项目名称###(不安全)"
  5. 点击允许"按钮.
  • 点击确定".
  • 复制 Web 应用程序的 URL.这就像 https://script.google.com/macros/s/###/exec.
    • 当您修改 Google Apps 脚本时，请重新部署为新版本.这样，修改后的脚本就会反映到 Web Apps 中.请注意这一点.

    4.使用网络应用程序运行该函数.

    这是用于请求 Web 应用程序的示例 curl 命令.请设置您的网络应用网址.

    4. Run the function using Web Apps.

    This is a sample curl command for requesting Web Apps. Please set your Web Apps URL.

    curl -L "https://script.google.com/macros/s/###/exec?url=https://docs.google.com/spreadsheets/d/e/2PACX-1vRrmEbjecLvXhbm409pa6JJXZd_ZXTG8Zt6OevIUs5Axq5oxlCZKU0QXk-2lW05HyXJ2B4Bzy3bG-4L/pubhtml"
    

    • 在这种情况下，GET 方法用于 Web 应用端.因此，您也可以使用浏览器直接访问上述网址.
    • 当您修改 Web Apps 的脚本时，请将 Web Apps 重新部署为新版本.这样，最新的脚本就会反映到 Web Apps 中.请注意这一点.

    • 在这个答案中，我认为您可能会从外部使用它.所以我使用了网络应用程序.如果你想直接从 Google Apps Script 中检索，你也可以使用下面的脚本.

    • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

    • In this answer, I thought that you might use this from outside. So I used Web Apps. If you want to directly retrieved from the Google Apps Script, you can also use the following script.

    function myFunction() {
      const inputUrl = "https://docs.google.com/spreadsheets/d/e/2PACX-1vRrmEbjecLvXhbm409pa6JJXZd_ZXTG8Zt6OevIUs5Axq5oxlCZKU0QXk-2lW05HyXJ2B4Bzy3bG-4L/pubhtml";
    
      const prop = PropertiesService.getScriptProperties();
      const ssId = prop.getProperty("ssId");
      if (ssId) {
        DriveApp.getFileById(ssId).setTrashed(true);
        prop.deleteProperty("ssId");
      }
      const re = new RegExp("(https?:\/\/docs\.google\.com\/spreadsheets\/d\/e\/2PACX-.+?\/)");
      if (!re.test(inputUrl)) throw new Error("Wrong URL.");
      const url = `${inputUrl.match(re)[1]}pub?output=xlsx`;
      const blob = UrlFetchApp.fetch(url).getBlob();
      const id = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "temp"}, blob).id;
      prop.setProperty("ssId", id);
      Drive.Revisions.update({published: true, publishedOutsideDomain: true, publishAuto: true}, id, 1);
      const sheets = SpreadsheetApp.openById(id).getSheets();
      const pubUrls = sheets.map(s => ({[s.getSheetName()]: `https://docs.google.com/spreadsheets/d/${id}/pubhtml?gid=${s.getSheetId()}`}));
      console.log(pubUrls);  // You can see the URLs for each sheet at the log.
    }
    

    • 网络应用
    • 利用 Google 的网络应用应用脚本
    • 高级 Google 服务
    • 通过 Google Apps 脚本发布 Google 电子表格

    作为另一种解决方法，当原始电子表格经常更改，并且原始电子表格中的工作表数量不变，然后您只想检索值时，您也可以使用以下脚本.在这个脚本中，即使再次运行脚本，URL 也不会改变.这样您就可以继续使用该网址.

    As another workaround, when the original Spreadsheet is often changed, and the number of sheet is constant in the original Spreadsheet, and then, you want to retrieve only values, you can also use the following script. In this script, the URL is not changed even when the script is run again. So you can continue to use the URL.

    function myFunction() {
      const inputUrl = "https://docs.google.com/spreadsheets/d/e/2PACX-1vRrmEbjecLvXhbm409pa6JJXZd_ZXTG8Zt6OevIUs5Axq5oxlCZKU0QXk-2lW05HyXJ2B4Bzy3bG-4L/pubhtml";
    
      const re = new RegExp("(https?:\/\/docs\.google\.com\/spreadsheets\/d\/e\/2PACX-.+?\/)");
      if (!re.test(inputUrl)) throw new Error("Wrong URL.");
      const url = `${inputUrl.match(re)[1]}pub?output=xlsx`;
      const blob = UrlFetchApp.fetch(url).getBlob();
      const prop = PropertiesService.getScriptProperties();
      let sheets;
      let ssId = prop.getProperty("ssId");
      if (ssId) {
        const temp = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "tempSpreadsheet"}, blob).id;
        const tempSheets = SpreadsheetApp.openById(temp).getSheets();
        sheets = SpreadsheetApp.openById(ssId).getSheets();
        tempSheets.forEach((e, i) => {
          const values = e.getDataRange().getValues();
          sheets[i].getRange(1, 1, values.length, values[0].length).setValues(values);
        });
        DriveApp.getFileById(temp).setTrashed(true);
      } else {
        ssId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: "copiedSpreadsheet"}, blob).id;
        Drive.Revisions.update({published: true, publishedOutsideDomain: true, publishAuto: true}, ssId, 1);
        prop.setProperty("ssId", ssId);
        sheets = SpreadsheetApp.openById(ssId).getSheets();
      }
      const pubUrls = sheets.map(s => ({[s.getSheetName()]: `https://docs.google.com/spreadsheets/d/${ssId}/pubhtml?gid=${s.getSheetId()}`}));
      console.log(pubUrls);  // You can see the URLs for each sheet at the log.
    }
    

    这篇关于链接到已发布的 Google 表格中的特定表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！