问使用google-api-php-client在特定的google电子表格上写入值。
EN

Stack Overflow用户

提问于 2016-08-24 17:44:29

回答 1查看 1.7K关注 0票数 0

我试图使用google-api-php-client定期从另一个源(MySQL查询)转储一些数据

转到特定的Google电子表格中

最后的结果应该是一个php脚本，它运行在我的服务器的cron中，定期将一些数据从SQL查询中转储到我的特定google电子表格中。

我有谷歌文档里的所有东西。

serviceID，Json键，电子表格ID，范围

实际上我能从我的电子表格中读到一个范围

问题是..。如何使用google客户端在电子表格中写入数据？

这是我用来从电子表格中读取数据的代码。

    <?php
error_reporting(E_ALL);
ini_set("display_errors", 1); 
set_include_path(get_include_path() . PATH_SEPARATOR . __DIR__.'/google-api-php-client/src');
require_once 'Google/autoload.php';


$key = json_decode(file_get_contents('myJasonkeyFile.json'));
$service_account_email='[SERVICE_EMAIL]@myproject.iam.gserviceaccount.com';
$appName='project-name';
$scopes = array(Google_Service_Sheets::SPREADSHEETS);


            $credentials = new Google_Auth_AssertionCredentials(
                $service_account_email,    // it's the client email
                $scopes,   // it's google spreadsheet scope
                $key->private_key         // here is the private key
            );

$client = new Google_Client();
$client->setAssertionCredentials($credentials);

if ($client->getAuth()->isAccessTokenExpired()) {
  $client->getAuth()->refreshTokenWithAssertion();
}

$client->setApplicationName($appName);
$service = new Google_Service_Sheets($client);


$spreadsheetId = '984984kjldkjldkd0984lkjdlkdj[randomkey]';
$range = 'hoja1!A1:C4';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);

$values = $response->getValues();

if (count($values) == 0) {
  print "No data found.\n";
} else {
  print "Name, Major:\n";
  foreach ($values as $row) {
    // Print columns A and E, which correspond to indices 0 and 4.
    printf("%s, %s\n", $row[0], $row[2]);
  }
}

提前感谢您的帮助

php

google-sheets

google-api

google-api-php-client

语音识别特惠，低至14.9元！

提供业界非常具有性价比的语音识别服务，超高识别准确率，适用多场景

回答 1

Stack Overflow用户

发布于 2016-11-07 05:21:16

此代码将向电子表格中添加一个包含所有信息的新行。我在这个部分使用了JS (参见最后一个函数，其中它提供了“value：[”)。

<script type="text/javascript">

    // Developer Console, https://console.developers.google.com
    var CLIENT_ID = '############-#########.apps.googleusercontent.com';
    var SECRET = '##################';
    var REDIRECT = 'http://#######.com';
    var AUTH_URL = 'https://accounts.google.com/o/oauth2/auth';
    var TOKEN_URL = 'https://www.googleapis.com/oauth2/v4/token';
    var SCOPES = ["https://www.googleapis.com/auth/spreadsheets"];
    var SPREADSHEET = '############';
    var LAST = 0;

    // var getCode = 'https://accounts.google.com/o/oauth2/auth?client_id='+ CLIENT_ID +'&scope='+ SCOPES +'&redirect_uri=http://localhost&response_type=code&access_type=offline';

    function checkAuth() {
        gapi.auth.authorize(
            {
                'client_id': CLIENT_ID,
                'scope': SCOPES.join(' '),
                'immediate': true
            }, handleAuthResult);
    }

    function handleAuthResult(authResult) {
        var authorizeDiv = document.getElementById('authorize-div');
        if (authResult && !authResult.error) {
            authorizeDiv.style.display = 'none';
            loadSheetsApi();
        } else {
            authorizeDiv.style.display = 'inline';
        }
    }

    function handleAuthClick(event) {
        gapi.auth.authorize({client_id: CLIENT_ID, scope: SCOPES, immediate: false}, handleAuthResult);
        return false;
    }

    function loadSheetsApi() {
        var discoveryUrl = 'https://sheets.googleapis.com/$discovery/rest?version=v4';
        gapi.client.load(discoveryUrl).then(setData);
    }

    function setData() {
        gapi.client.sheets.spreadsheets.values.get({
            spreadsheetId: SPREADSHEET,
            range: 'Sheet1',
        }).then(function(response) {
            var range = response.result;

            if (range.values.length > 0) {
                LAST = (range.values.length + 1);
            }
        }).then(function() {
            gapi.client.sheets.spreadsheets.values.update({
                spreadsheetId: SPREADSHEET,
                valueInputOption: 'USER_ENTERED',
                values: [ ["<?php echo $name; ?>", "<?php echo $email; ?>", "<?php echo $subject; ?>", "<?php echo $category; ?>", "<?php echo $iama; ?>", "<?php echo $country; ?>", "<?php echo $message; ?>"] ],
                range: 'Sheet1!A' + window.LAST,
            }).then(function(response) {
                // console.log('update last: ' + window.LAST);
            });
        });
    }

</script>