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

问题描述

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

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.

我正在寻找指向已发布的工作簿特定工作表的链接.我编写了一个简单的工作簿进行测试，发布的链接如下所示:

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

• You want to retrieve the values from Sheet2 from the URL of https://docs.google.com/spreadsheets/d/e/2PACX-1vRrmEbjecLvXhbm409pa6JJXZd_ZXTG8Zt6OevIUs5Axq5oxlCZKU0QXk-2lW05HyXJ2B4Bzy3bG-4L/pubhtml.
• The owner of this Spreadsheet is not you.
• You don't know the Spreadsheet ID and each sheet ID in the Spreadsheet. You know only the URL of https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
• Under above situation, you want to retrieve the direct URL of the sheet 2.

对于上述目标，这个答案如何?

For above goal, how about this answer?

不幸的是，在当前阶段，似乎无法直接从 https://docs.google.com/spreadsheets/d/e/2PACX-#的URL检索电子表格ID和每个电子表格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脚本创建的Web Apps实现您的目标.使用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 Spreadsheet.
2. 将XLSX数据转换为Google Spreadsheet.
3. 将转换后的Google Spreadsheet发布到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脚本项目.

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

如果您想直接创建它，请访问 https://script.new/.在这种情况下，如果您未登录Google，则会打开登录"屏幕.因此，请登录到Google.这样，将打开Goog​​le Apps脚本的脚本编辑器.

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 Apps.

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

    4.使用Web Apps运行该功能.

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

    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"
    

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

    • 在这个答案中，我认为您可以从外部使用它.因此，我使用了Web Apps.如果您想直接从Google Apps脚本中检索，也可以使用以下脚本.

    • 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的Web Apps优势Apps脚本
    • 高级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屋！