Google API如何连接以从电子表格中接收值 [英] Google API How to connect to receive values from spreadsheet
问题描述
我创建了几个Google Doc电子表格，我授权其他用户编辑。
我需要的是使用PHP以编程方式从这些电子表格中检索信息。然而，我不知道如何连接，甚至开始检索。
这是我到目前为止做的：
1 - 安装Google PHP API库。
2 - 在同一个帐户中创建一个Google API项目。我不知道我需要哪个API以及我需要哪个oAuth键。
3 - 从 https://github.com/asimlqt/php-google-spreadsheet-client 。
那么，现在呢？如何发送API命令来检索我想要的电子表格。我不确定如何进行身份验证以及如何检索。到目前为止，我使用Google Drive的API Server Key尝试了下面的内容......这只是一个猜测。我在Google API Spreadsheet客户端的示例中复制并粘贴了以下代码：
<？php
require_once' PHP-谷歌的电子表格的客户端 - master\src\Google\Spreadsheet\Autoloader.php';
$ accessToken ='xxxxxxxxxxxxxxxxxxxxxxx';
$ request = new Google\Spreadsheet\Request（$ accessToken）;
$ serviceRequest = new Google\Spreadsheet\DefaultServiceRequest（$ request）;
Google\Spreadsheet\ServiceRequestFactory :: setInstance（$ serviceRequest）;
$ spreadsheetService = new Google\Spreadsheet\SpreadsheetService（）;
$ spreadsheetFeed = $ spreadsheetService-> getSpreadsheets（）;
？>
我收到以下错误：
致命错误：在C：\php\php-google-spreadsheet-client-master\src\中未收集异常'Exception'，消息'String can not be parsed as XML' Google \ Spreadsheet \ SpreadsheetFeed.php：43 Stack trace：＃0 C：\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetFeed.php（43）： SimpleXMLElement-> __ construct（''）＃1 C：\ phpp \ php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetService.php（39）：Google\Spreadsheet\\ \\ SpreadsheetFeed-> __ construct（false）＃2 C：\ php_\\google_docd.php（11）：Google \ Spreadsheet \ SpreadsheetService-> getSpreadsheets（）＃3 {main}抛出C：\ php \ php-google-spreadsheet-client-master \src\Google\Spreadsheet\SpreadsheetFeed.php on line 43
请。清除指示。我是一个完整的Google API新手。谢谢。有关如何在SOAPUI中测试或通过bash进行测试的示例也很有帮助，因为我可以使用它来计算如何发出Curl请求。非常感谢！
我终于搞定了，尽管文档中的所有尝试都使它看起来像不可能，这里是我的setup：
- OAuth2的Google API for php lib;此处： 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（红色按钮），然后选择服务帐户。你会下载一个文件，保持安全。您将使用您的脚本上传它。
此外，请注意：除非：我的文档是旧电子表格，否则它永远不会工作。我还需要与Google的控制台上生成的用户ID（可能是电子邮件的用户ID）共享电子表格文档。
该文档包含一个用适当的列名称（名称，年龄）冻结的顶行。
以下是我使用的php脚本与以上：
<？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 ='fill_with_info_from_console@developer.gserviceaccount.com';
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（新的Google_AssertionCredentials（
G_CLIENT_EMAIL，
数组（'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 Google\Spreadsheet\Request（$ accessToken）;
$ serviceRequest = new Google\Spreadsheet\DefaultServiceRequest（$ request）;
Google\Spreadsheet\ServiceRequestFactory :: setInstance（$ serviceRequest）;
$ spreadsheetService = new Google\Spreadsheet\SpreadsheetService（）;
$ spreadsheetFeed = $ spreadsheetService-> getSpreadsheets（）;
$ spreadsheet = $ spreadsheetFeed-> getByTitle（'title_of_the_spreadsheet_doc'）;
$ worksheetFeed = $ spreadsheet-> getWorksheets（）;
$ worksheet = $ worksheetFeed-> getByTitle（'title_of_the_tab'）;
$ listFeed = $ worksheet-> getListFeed（）;
//下面的这个位会创建一个新行，只有当你有一个合适的第一行标记为
$ row = array（'name'=>'John '，'年龄'=> 25）;
$ listFeed-> insert（$ row）;
？>
我还应该注意： 所有这些工作仍在进行中，但希望能帮助某人为任何人写出令人惊叹的指示，以便更好地理解本质。 它是来自Google文档的一些小部分的汇编，一些关于stackoverflow的答案以及来自2 api库的信息。 这已经非常糟糕了痛苦得到这个工作，它真的不应该;我假设这是因为谷歌正在同时转换认证，控制台接口和api版本。 编辑：看来列中的名称谷歌文档是受限制的：没有空间允许（？），不允许下划线（？），CamelCase似乎很麻烦。我只设法让破折号工作，就像在placement-other中一样，否则api会抛出一些未捕获的异常。 编辑：我用完全相同的设置为一个新的项目，并仍然工作，与谷歌最近推出新的电子表格模型。什么让我忘记了我已经忘记了：没有允许空白单元格，标题必须被冻结而没有空格，并且从PHP查询时它们是小写字母。
希望这有助于！
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.
I created several Google Doc spreadsheets which I granted permission to other users to edit.
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.
Here is what I did so far:
1 - Installed the Google PHP API libraries.
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 - Installed the Google API Spreadsheet client from https://github.com/asimlqt/php-google-spreadsheet-client.
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-master\src\Google\Spreadsheet\Autoloader.php';
$accessToken = 'xxxxxxxxxxxxxxxxxxxxxxx';
$request = new Google\Spreadsheet\Request($accessToken);
$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($request);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
?>
I receive the following error:
Fatal error: Uncaught exception 'Exception' with message 'String could not be parsed as XML' in C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetFeed.php:43 Stack trace: #0 C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetFeed.php(43): SimpleXMLElement->__construct('') #1 C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetService.php(39): Google\Spreadsheet\SpreadsheetFeed->__construct(false) #2 C:\php\google_docd.php(11): Google\Spreadsheet\SpreadsheetService->getSpreadsheets() #3 {main} thrown in C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetFeed.php on line 43
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:
- google api for OAuth2 php lib; here: https://code.google.com/p/google-api-php-client/
- google spreadsheet api php lib; here: https://github.com/asimlqt/php-google-spreadsheet-client
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.
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).
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 = 'fill_with_info_from_console@developer.gserviceaccount.com';
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 Google\Spreadsheet\Request($accessToken);
$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($request);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$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);
?>
I should also note:
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
It's a compilation of bits and pieces from both the google documentations, some answers on stackoverflow and info from the 2 api libraries
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.
EDIT: 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"
EDIT: 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.
Hope this helps!
这篇关于Google API如何连接以从电子表格中接收值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
