Google API 如何连接以从电子表格接收值 [英] Google API How to connect to receive values from spreadsheet

问题描述

我开始这个项目时认为它很简单.几个小时后，我意识到 Google API 有点像迷宫，有多个 API 和库.我真的需要请明确说明如何执行此操作.

I embarked on this project thinking it would be simple. Many hours later I'm realizing the Google API is a bit of a labyrinth with multiple APIs and libraries. I really need please clear directions on how to go about doing this.

我创建了多个 Google Doc 电子表格，并授予其他用户编辑权限.

I created several Google Doc spreadsheets which I granted permission to other users to edit.

我需要的只是使用 PHP 以编程方式从这些电子表格中检索信息.但是，我不知道如何连接甚至开始检索.

All I need is to programatically retrieve the info from these spreadsheets using PHP. However, I can't figure out how to connect to even start the retrieval.

这是我目前所做的:

1 - 安装了 Google PHP API 库.

1 - Installed the Google PHP API libraries.

2 - 在同一个帐户中创建了一个 Google API 项目.我不知道我需要哪个 API 以及我需要哪些 oAuth 密钥.

2 - Created a Google API project in the same account. I have no idea which API I need and which oAuth keys I need.

3 - 从 https://github.com/asimlqt 安装 Google API 电子表格客户端/php-google-spreadsheet-client.

好吧，现在呢?如何发送 API 命令来检索我想要的电子表格.我不确定如何进行身份验证以及如何检索.到目前为止，我使用 Google Drive 的 API 服务器密钥尝试了以下操作......这只是一个猜测.我从 Google API 电子表格客户端中的示例中复制并粘贴了以下内容:

Well, now what? How do I send the API command to retrieve the spreadsheet I want. I'm unsure how to authenticate and how to retrieve. So far, I tried the below using the API Server Key for Google Drive....this was just a guess. I copied and pasted the below from the example in the Google API Spreadsheet client:

<?php
require_once 'php-google-spreadsheet-client-mastersrcGoogleSpreadsheetAutoloader.php';

$accessToken = 'xxxxxxxxxxxxxxxxxxxxxxx';
$request = new GoogleSpreadsheetRequest($accessToken);
$serviceRequest = new GoogleSpreadsheetDefaultServiceRequest($request);
GoogleSpreadsheetServiceRequestFactory::setInstance($serviceRequest);


$spreadsheetService = new GoogleSpreadsheetSpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
?>

我收到以下错误:

Fatal error: Uncaught exception 'Exception' with message 'String could not be parsed as XML' in C:phpphp-google-spreadsheet-client-mastersrcGoogleSpreadsheetSpreadsheetFeed.php:43 Stack trace: #0 C:phpphp-google-spreadsheet-client-mastersrcGoogleSpreadsheetSpreadsheetFeed.php(43): SimpleXMLElement->__construct('') #1 C:phpphp-google-spreadsheet-client-mastersrcGoogleSpreadsheetSpreadsheetService.php(39): GoogleSpreadsheetSpreadsheetFeed->__construct(false) #2 C:phpgoogle_docd.php(11): GoogleSpreadsheetSpreadsheetService->getSpreadsheets() #3 {main} thrown in C:phpphp-google-spreadsheet-client-mastersrcGoogleSpreadsheetSpreadsheetFeed.php on line 43  

请，请.明确指示.我是一个完整的 Google API 新手.谢谢.关于如何在 SOAPUI 中或通过 bash 进行测试的示例也会有所帮助，因为我可以使用它来确定如何发出 Curl 请求.非常感谢！

Please, please. Clear instructions. I'm a complete Google API newbie. thanks. Examples on how to test in SOAPUI or via bash would also be helpful as I can then use that to figure out how to issue Curl request. Many thanks!

推荐答案

我终于让它工作了，尽管文档中的所有尝试使它看起来不可能，但这是我的设置:

I finally got it working, despite all attempts from the documents to make it look like impossible, here is my setup:

• 用于 OAuth2 php lib 的 google api；此处:https://code.google.com/p/google-api-php-client/
• google 电子表格 api php lib；此处:https://github.com/asimlqt/php-google-spreadsheet-client

您需要在 API 控制台上创建凭据:https://console.developers.google.com/在那里，您首先需要创建一个项目并为您的应用程序创建一组经过身份验证的信息:在左侧菜单中，单击 API &身份验证，然后是凭据.单击创建新客户端 ID(红色按钮)，然后选择服务帐户.您将下载一个文件，请妥善保管.您将使用脚本上传它.

You will need to create the credentials on the API Console: https://console.developers.google.com/ There you will need to first create a project and create set of authentified information for your app: in the left menu, click on API & Auth, then Credentials. Click on Create New Client ID (red button), then select Service Account. You will download a file, keep it safe. you will upload it with your script.

另外，请注意:它从来没有工作过，除非:我的文档是旧电子表格".我还需要与在 Google 控制台上生成的用户 ID(可以是电子邮件)共享电子表格文档.文档包含用适当的列名称(姓名、年龄)冻结的顶行.

Also, please note: it never worked unless: my document was the 'old spreadsheet'. I also needed to share the spreadsheet document with the user ID (the one that could be an email) generated on Google's console. The document contains a top row that is frozen with the appropriate columns name (name, age).

这里是我结合上面使用的php脚本:

Here is the php script I used in combination with the above:

<?php
require_once 'php-google-spreadsheet/src/Google/Spreadsheet/Autoloader.php';
require_once 'google-api-php-client/src/Google_Client.php';

const G_CLIENT_ID       = 'fill_with_info_from_console.apps.googleusercontent.com';
const G_CLIENT_EMAIL    = '[e-mail]';
const G_CLIENT_KEY_PATH = 'key/keep_the_complex_filename_here_privatekey.p12';
const G_CLIENT_KEY_PW   = 'notasecret';

$obj_client_auth  = new Google_Client ();
$obj_client_auth -> setApplicationName ('test_or_whatever_you_like');
$obj_client_auth -> setClientId (G_CLIENT_ID);
$obj_client_auth -> setAssertionCredentials (new Google_AssertionCredentials (
    G_CLIENT_EMAIL, 
    array('https://spreadsheets.google.com/feeds','https://docs.google.com/feeds'), 
    file_get_contents (G_CLIENT_KEY_PATH), 
    G_CLIENT_KEY_PW
));
$obj_client_auth -> getAuth () -> refreshTokenWithAssertion ();
$obj_token  = json_decode ($obj_client_auth -> getAccessToken ());
$accessToken = $obj_token->access_token;

$request = new GoogleSpreadsheetRequest($accessToken);
$serviceRequest = new GoogleSpreadsheetDefaultServiceRequest($request);
GoogleSpreadsheetServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new GoogleSpreadsheetSpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('title_of_the_spreadsheet_doc');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('title_of_the_tab');
$listFeed = $worksheet->getListFeed();

// this bit below will create a new row, only if you have a frozen first row adequatly labelled

$row = array('name'=>'John', 'age'=>25);
$listFeed->insert($row);

?>

我还应该注意:

• 所有这些工作仍在进行中，但希望能帮助某人写出令人惊叹的说明，让任何人更好地理解细节

• All this is still very much work in progress, but hopefully will help someone write amazing instructions for anyone to better understand the nitty gritty

它是谷歌文档中的点点滴滴的汇编，一些关于 stackoverflow 的答案以及来自 2 个 api 库的信息

It's a compilation of bits and pieces from both the google documentations, some answers on stackoverflow and info from the 2 api libraries

让这个工作非常痛苦，而且真的不应该；我假设这是因为谷歌正在同时转换身份验证、控制台界面和 API 版本.

It has been an awfully painful to get this working and it really shouldn't; i'm assuming this is because google is transitioning authentification, console interface and api versions all at the same time.

似乎谷歌文档中的列名称受到限制:不允许使用空格(?)，不允许使用下划线(?)，CamelCase 似乎很麻烦.我只设法让破折号起作用，就像在放置其他"中一样，否则 api 会抛出一些未捕获的异常"

It seems the columns names in the google doc are restricted: no space allowed(?), no underscore allowed (?), CamelCase seems troublesome. I only managed to get the dashes to work, like in "placement-other", otherwise the api throws some "Uncaught exception"

我对一个新项目使用了完全相同的设置，并且仍然可以工作，谷歌最近推出了新的电子表格模型.什么让我忘记了:不允许空白单元格，标题必须冻结没有空格，并且从 PHP 查询时它们是小写的.

I used the exact same setup for a new project and still does work, with the new Spreadsheet model introduced by Google recently. What hold me that i had forgotten: no blank cell allowed, headers must be frozen without spaces, and they are lowercase when queried from PHP.

希望这有帮助！

这篇关于Google API 如何连接以从电子表格接收值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！