write values on specific google spreadsheet with google-api-php-client

Question

I'm trying to periodically dump some data from another source (MySQL query) using google-api-php-client

into a specific Google spreadsheet

Final result should be a php script that runs in the cron of my server to periodically dump some data from a SQL query to my specific google spreadsheet.

I have everything from google documentation.

serviceID, Json key, Spreadsheet ID, Range

I'm actually able to read a range from my spreadsheet

The question is... How do I write data in my spreadsheet using google-api-php-client?

This is the code I'm using to read data from the spreadsheet.

    <?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]);
  }
}

Thanks in advance for your help

I found myself an answer. http://stackoverflow.com/questions/38025841/trying-to-append-a-row-to-a-google-spreadsheet-in-php?rq=1 — Daniel Ibars, Aug 31 '16 at 21:22
I tried previous link and only worked for a single row. Does anybody have a better approach to write several rows in the same request? — Daniel Ibars, Sep 06 '16 at 02:50

score 0 · Answer 1 · answered Nov 07 '16 at 13:21

This code will add a new row to spreadsheet with all info. I used JS for this part (See last function where it sais "values: ["

<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>

hey guy, thanks for your help, just to complement your awser, when you go add the range parameter, if you set "A1" this know to set of last row — Alisom Martins, Nov 11 '16 at 13:30

write values on specific google spreadsheet with google-api-php-client

1 Answers1