如何使用Python写入公开可用的Google工作表(未经授权)? [英] How can one write to a publicly available Google sheet (without authorization) in Python?

问题描述

我对非我所有的Google表格具有完全的编辑权限.我希望能够在未经Google API授权的情况下使用Python写入电子表格.我检查了几个可用的软件包( gdata ， gspread 等)，看来它们都要求提供凭据.

我还可以使用熊猫的 requests 或 pd.read_csv()来未经授权阅读电子表格的内容(我通过更改最后一部分来调整URL说... edit#gid = ...到... export?format = csv& gid = ...).但是，将POST请求发送到相同的URL时，我收到了 200状态代码，但收到的是相同的旧的空电子表格.

我们非常感谢您的帮助.

解决方案

我相信您的目标如下.

• 您要使用python将值放入公开共享的Google Spreadsheet.
• 在这种情况下，您要未经授权访问电子表格.

为此，这个答案如何?

问题和解决方法:

为了将值放入公开共享的Google Spreadsheet，使用了POST方法.在这种情况下，需要使用访问令牌.另一方面，在GET方法的情况下，当使用Sheets API时，可以使用API​​密钥.像 exportLinks 之类的端点，您可以在没有API密钥的情况下检索值.这些是Google方面的规范.

在这种情况下，为了实现您的目标，我想提出以下两种模式.

模式1:

在这种模式下，我想建议使用从服务帐户获取的访问令牌访问电子表格.在这种情况下，脚本可以更简单.

示例脚本:

 导入gspread从oauth2client.service_account导入ServiceAccountCredentialseBookId ="###"#请设置电子表格ID.范围= ['https://www.googleapis.com/auth/spreadsheets']凭据= ServiceAccountCredentials.from_json_keyfile_name('credentials.json'，作用域)客户端= gspread.authorize(凭证)电子表格= client.open_by_key(spreadsheetId)工作表=电子表格.sheet1worksheet.update_acell('A1'，'sample') 

• 在此示例脚本中，使用gspread将 sample 放入公共共享电子表格中第一个选项卡的单元格"A1".

模式2:

在这种模式下，我想建议使用Google Apps脚本创建的Web Apps作为包装API来访问电子表格.在这种情况下，python脚本会更简单.

用法:

请执行以下流程.

1.创建Google Apps脚本的新项目.

Web Apps的示例脚本是Google Apps脚本.因此，请创建一个Google Apps脚本项目.

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

2.准备脚本.

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

  function doPost(e){尝试 {const电子表格ID = e.parameter.spreadsheetId;const obj = JSON.parse(e.postData.contents);const resource = obj.body;const range = obj.arguments.range;const valueInputOption = obj.arguments.valueInputOption;Sheets.Spreadsheets.Values.update(resource，电子表格ID，范围，{valueInputOption:valueInputOption});返回ContentService.createTextOutput("ok");} catch(e){返回ContentService.createTextOutput(JSON.stringify(e));}} 

• 在这种情况下，将使用POST方法.
• 在此示例脚本中，作为测试脚本，使用Sheets API中的sheets.values.update方法将值放入电子表格.

3.部署Web应用.

1. 在脚本编辑器上，通过发布"->作为Web应用程序部署"打开一个对话框.
2. 为以以下方式执行应用":选择我" .
   • 通过这种方式，脚本以所有者身份运行.
3. 为谁有权访问该应用程序:" 选择任何人，甚至是匿名的" .
   • 在这种情况下，不需要请求访问令牌.我认为我建议您将此设置用于您的目标.
   • 当然，您也可以使用访问令牌.那时，请将其设置为任何人" .
4. 单击部署"按钮作为新的项目版本".
5. 自动打开需要授权"对话框.
   1. 点击查看权限".
   2. 选择自己的帐户.
   3. 在此应用未验证"中单击高级".
   4. 点击转到###项目名称###(不安全)"
   5. 单击允许"按钮.

6. 单击确定".
7. 复制Web应用程序的URL.就像 https://script.google.com/macros/s/###/exec .
   • 修改Google Apps脚本后，请重新部署为新版本.这样，修改后的脚本将反映到Web Apps.请注意这一点.

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

这是用于请求Web应用程序的示例python脚本.请设置您的Web Apps URL，电子表格ID和范围.

  import json汇入要求eBook_id ='###'#请设置电子表格ID.身体= {"arguments":{"range":"Sheet1！A1"，"valueInputOption":"USER_ENTERED"}，"body":{"values":[["sample"]]}}url ='https://script.google.com/macros/s/###/exec?spreadsheetId='+电子表格IDres = requests.post(URL，json.dumps(body)，headers = {'Content-Type':'application/json'})打印(res.text) 

• 在此示例脚本中，将 sample 放置到公开共享的电子表格中第一个标签的单元格"A1"中.
• 在这种情况下，不需要在python脚本中进行任何授权，因为在部署Web Apps时已经完成了授权.

注意:

• 修改Web应用程序的脚本后，请重新部署Web应用程序为新版本.这样，最新脚本将反映到Web应用程序中.请注意这一点.

参考:

• 网络应用
• 利用Google的Web Apps优势Apps脚本
• 高级Google服务
• 通过Google Apps脚本发布Google电子表格
• spreadsheets.values.update

I have full edit access to a Google Sheet not owned by me. I want to be able to write to the spreadsheet using Python without Google API authorization. I checked several available packages (gdata, gspread etc.) and seems all of them ask for the credentials.

I was also able to read the content of a spreadsheet without authorization using requests or pd.read_csv() by pandas (I tweaked the URL by changing the last part saying ...edit#gid=... to ...export?format=csv&gid=...). Yet, when sending a POST request to the same URL I received 200 status code but the same old empty spreadsheet.

Any help is highly appreciated.

解决方案

I believe your goal as follows.

• You want to put the values to the publicly shared Google Spreadsheet using python.
• In this case, you want to access to the Spreadsheet without authorization.

For this, how about this answer?

Issue and workaround:

In order to put the values to the publicly shared Google Spreadsheet, the POST method is used. In this case, it is required to use the access token. On the other hand, in the case of the GET method, when the Sheets API is used, an API key can be used. And the endpoints like exportLinks, you can retrieve the values without the API key. These are the specification of Google side.

Under this condition, in order to achieve your goal, I would like to propose the following 2 patterns.

Pattern 1:

In this pattern, I would like to propose to access to the Spreadsheet using the access token retrieved from the service account. In this case, the script can be simpler.

Sample script:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

spreadsheetId = "###"  # Please set the Spreadsheet ID.

scope = ['https://www.googleapis.com/auth/spreadsheets']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.sheet1
worksheet.update_acell('A1', 'sample')

• In this sample script, sample is put to the cell "A1" of the 1st tab in the publicly shared Spreadsheet using gspread.

Pattern 2:

In this pattern, I would like to propose to access to the Spreadsheet using the Web Apps created by Google Apps Script as the wrapper API. In this case, the python script is more simpler.

Usage:

Please do the following flow.

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of 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.

2. Prepare script.

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

function doPost(e) {
  try {
    const spreadsheetId = e.parameter.spreadsheetId;
    const obj = JSON.parse(e.postData.contents);
    const resource = obj.body;
    const range = obj.arguments.range;
    const valueInputOption = obj.arguments.valueInputOption;
    Sheets.Spreadsheets.Values.update(resource, spreadsheetId, range, {valueInputOption: valueInputOption});
    return ContentService.createTextOutput("ok");
  } catch(e) {
    return ContentService.createTextOutput(JSON.stringify(e));
  }
}

• In this case, the POST method is used.
• In this sample script, as a test script, the values are put to the Spreadsheet using the method of spreadsheets.values.update in Sheets API.

3. Deploy Web Apps.

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.
3. Select "Anyone, even anonymous" for "Who has access to the app:".
   • 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".
4. Click "Deploy" button as new "Project version".
5. Automatically open a dialog box of "Authorization required".
   1. Click "Review Permissions".
   2. Select own account.
   3. Click "Advanced" at "This app isn't verified".
   4. Click "Go to ### project name ###(unsafe)"
   5. Click "Allow" button.

6. Click "OK".
7. 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. Run the function using Web Apps.

This is a sample python script for requesting Web Apps. Please set your Web Apps URL, Spreadsheet ID and range.

import json
import requests

spreadsheet_id = '###'  # Please set the Spreadsheet ID.
body = {
    "arguments": {"range": "Sheet1!A1", "valueInputOption": "USER_ENTERED"},
    "body": {"values": [["sample"]]}
}
url = 'https://script.google.com/macros/s/###/exec?spreadsheetId=' + spreadsheet_id
res = requests.post(url, json.dumps(body), headers={'Content-Type': 'application/json'})
print(res.text)

• In this sample script, sample is put to the cell "A1" of the 1st tab in the publicly shared Spreadsheet.
• In this case, no authorization is required in the python script, because it has already been done when Web Apps is deployed.

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.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• Advanced Google services
• publish a Google Spreadsheet through Google Apps Scripts
• spreadsheets.values.update

这篇关于如何使用Python写入公开可用的Google工作表(未经授权)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！