更新时间:2023-01-16 21:20:42
我相信你的目标如下.
当 Google 电子表格与 Sheets API 一起使用时,需要使用访问令牌和 API 密钥.当您想在不使用访问令牌和 API 密钥的情况下从 Google 电子表格中检索值时,在此答案中,我想提出以下使用由 Google Apps 脚本创建的 Web 应用程序的解决方法.此变通方法的流程如下.
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.
请执行以下流程.
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 脚本)复制并粘贴到脚本编辑器中.此脚本适用于 Web 应用程序.
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);
}
https://www.googleapis.com/auth/drive.readonly
和 https://www.googleapis.com/auth/drive
的范围包含到访问令牌.这些范围是访问网络应用所必需的.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.https://script.google.com/macros/s/###/exec
.您可以使用以下脚本从 Google 电子表格中检索值.
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 Apps,您也可以使用以下 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"