不使用Sheets API从Google表格获取数据 [英] Get data from Google Sheets without Sheets API

问题描述

我已经创建了一个应用程序(React Native)，用于显示Google表单的响应，因此我创建了响应表.

I have created an app (React Native) where I am required to show the responses of a google form, so I created the Responses Sheet.

尽管Sheets API是最好的解决方案，但我不希望使用它(除非没有其他选择).

Though Sheets API would be the best solution, I do not wish to use it (unless there is no other choice).

有没有一种方法可以在不使用Google API的情况下从工作表中获取数据(csv/json/arrays?/其他)?我只想要单元格中的值，什么都没有.

Is there a way to get the data (csv/json/arrays?/anything else) from the sheet without using Google API? I only want the values from the cells, nothing else.

注意:

• 我想用JavaScript解析数据.

推荐答案

我相信您的目标如下.

• 您希望不使用Sheets API从Google Spreadsheet检索值.
  • 我了解在这种情况下，您希望不使用访问令牌和API密钥来检索值.
  • You want to retrieve the values from Google Spreadsheet without using Sheets API.
    • I understood that in this case, you want to retrieve the values without using the access token and API key.

    将Google Spreadsheet与Sheets API一起使用时，需要使用访问令牌和API密钥.当您想不使用访问令牌和API密钥而从Google Spreadsheet检索值时，在此答案中，我想提出以下解决方法，使用由Google Apps脚本创建的Web Apps.此解决方法的流程如下.

    When Google Spreadsheet is used with Sheets API, the access token and the API key are required to be used. When you want to retrieve the values from Google Spreadsheet without using the access token and the API key, in this answer, I would like to propose the following workaround using Web Apps created by Google Apps Script. The flow of this workaround is as follows.

    1. 从Javascript请求到Web Apps.
    2. 在Web Apps中，这些值是从Google Spreadsheet返回的.
       • 在这种解决方法中，不需要公开共享Google Spreadsheet.
    1. Request to Web Apps from Javascript.
    2. At Web Apps, the values are returned from Google Spreadsheet.
       • In this workaround, the Google Spreadsheet is not required to be shared publicly.

    用法:

    请执行以下流程.

    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脚本)并将其粘贴到脚本编辑器中.该脚本适用于Web Apps.

    Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps.

    function doGet(e) {
      const id = e.parameter.spreadsheetId;
      const sheetName = e.parameter.sheetName;
      const sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
      const values = sheet.getDataRange().getValues();
      return ContentService.createTextOutput(JSON.stringify({values: values})).setMimeType(ContentService.MimeType.JSON);
    }
    

    3.部署Web应用.

    1. 在脚本编辑器上，通过发布"打开对话框. -> 部署为网络应用".
    2. 选择我" 作为将应用程序执行为:" .
       • 通过这种方式，脚本以所有者身份运行.
    1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
    2. Select "Me" for "Execute the app as:".
       • By this, the script is run as the owner.
    • 在这种情况下，不需要请求访问令牌.我认为我建议您将此设置用于您的目标.
    • 当然，您也可以使用访问令牌.那时，请将其设置为任何人" .并且请在访问令牌中包含https://www.googleapis.com/auth/drive.readonly和https://www.googleapis.com/auth/drive的范围.这些范围是访问Web应用程序所必需的.
    • In this case, no access token is required to be request. I think that I recommend this setting for your goal.
    • Of course, you can also use the access token. At that time, please set this to "Anyone". And please include the scope of https://www.googleapis.com/auth/drive.readonly and https://www.googleapis.com/auth/drive to the access token. These scopes are required to access to Web Apps.
    1. 点击查看权限".
    2. 选择自己的帐户.
    3. 点击高级"在此应用未验证"中.
    4. 点击转到###项目名称###(不安全)"
    5. 点击允许"按钮.

  • 点击确定".
  • 复制Web应用程序的URL.就像https://script.google.com/macros/s/###/exec.
    • 修改Google Apps脚本后，请重新部署为新版本.这样，修改后的脚本将反映到Web Apps.请注意这一点.
    • Click "OK".
    • Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
      • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

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

      您可以使用以下脚本从Google Spreadsheet中检索值.

      4. Run the function using Web Apps.

      You can retrieve the values from Google Spreadsheet using the following script.

      const baseUrl = "https://script.google.com/macros/s/###/exec";  // Please set your Web Apps URL.
      const para = {
        spreadsheetId: "###",  // Please set your Google Spreadsheet ID.
        sheetName: "Sheet1"  // Please set the sheet name you want to retrieve the values.
      };
      const q = new URLSearchParams(para);
      const url = baseUrl + "?" + q;
      fetch(url)
        .then(res => res.json())
        .then(res => {
          const values = res.values;
          console.log(values);
        });
      

      如果要使用curl命令测试上述Web应用程序，还可以使用以下curl命令.

      If you want to test the above Web Apps using a curl command, you can also use the following curl command.

      $ curl -L "https://script.google.com/macros/s/###/exec?spreadsheetId=###&sheetName=Sheet1"
      

      注意:

      • 修改Web应用程序的脚本后，请重新部署Web应用程序为新版本.这样，最新脚本将反映到Web应用程序中.请注意这一点.
      • 最近，在我的环境中，我已经确认，当使用Javascript从公开共享的电子表格和已发布的电子表格中检索值时，会发生与CORS相关的错误.我担心在您的环境中也可能会发生此类问题.因此，为了实现您的目标，我建议使用Web Apps.在这种情况下，我已经确认没有错误发生.

      Note:

      • 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 my environment, recently, I have confirmed that when the values are retrieved from the publicly shared Spreadsheet and the published Spreadsheet to Web using Javascript, the error related to CORS occurs. I'm worry that such issue might also occur at your environment. So in order to achieve your goal, I proposed to use Web Apps. In this case, I have already confirmed that no error occurs.
      • 网络应用
      • 利用Google的Web Apps优势Apps脚本
      • Web Apps
      • Taking advantage of Web Apps with Google Apps Script

      这篇关于不使用Sheets API从Google表格获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！