Google Spreadsheets Api V4 -> Get Background? PHP

Question

I installed the below:

https://github.com/google/google-api-php-client-services

Which is using

https://github.com/google/google-api-php-client-services/tree/master/src/Google/Service/Sheets

What I want to do is:

$cellFormat = new Google_Service_Sheets_CellFormat();
$color = $cellFormat->getBackgroundColor();

But I can't figure out how to assign the spreadsheet or select the range. And then of course return the response.

Any help would be greatly appreciated!

IT Helper IT Helper · Accepted Answer · 2017-03-20T19:19:54

I was unable to find the any documentation for the syntax of google spreadsheets v4 so I solved it using a google APP Script.

First I get the sheet:

    var Sheet = SpreadsheetApp.openById("XXXXXXSPREADSHEETXXXXXID").getSheets();

var tracker = "NEWXXXXXXSPREADSHEETXXXXXID";
var date = new Date();
var M = date.getMonth();
var Y = date.getFullYear();


var Sheettemp = SpreadsheetApp.openById(tracker);
var SheetTr = Sheettemp.getSheets()[M];

var templateSheet = Sheettemp.getSheetByName('Empty');
Sheettemp.insertSheet(M, {template: templateSheet});
Sheettemp.deleteSheet(SheetTr);

SheetTr = Sheettemp.getSheets()[M];

SheetTr.setName(months[M]);



var ctrak = 1;
var ctrak2 = 1;

M = M + 1;

Then I Cycle for the date and record this to an array:

    for (var S in Sheet) {

    var A = Sheet[S].getName();

    var data = Sheet[S].getRange("D:D").getValues();

    var start = M + '/' + 1 + '/' + Y;
    var sdate = new Date(start);
    var step = [];
    var lastdate = "";


    for (var i in data) {
        var dateval = data[i][0];
        var dchk = new Date(dateval);
        if (!isNaN(dchk.valueOf())) {


            if (lastdate === "") {
                if (dchk.getMonth() === sdate.getMonth()) {
                    lastdate = dchk;
                    start = i;
                    var MM = (dchk.getMonth() + 1);
                    var DD = dchk.getDate();
                    var YY = dchk.getFullYear();

                    var arrdate = MM + '/' + DD + '/' + YY;
                    var j = parseInt(i) + 1;
                    step.push([j, arrdate]);
                }

            } else {
                if (dchk.toString() !== lastdate.toString()) {

                    lastdate = dchk;

                    var stplng = step.length - 1;
                    if (stplng >= 0) {
                        step[step.length - 1][2] = (i);
                    }

                    var MM = (dchk.getMonth() + 1);
                    var DD = dchk.getDate();
                    var YY = dchk.getFullYear();

                    var arrdate = MM + '/' + DD + '/' + YY;
                    var j = parseInt(i) + 1;
                    step.push([j, arrdate]);
                    start = i;
                }
            }
        }
    }

Then for every date I get the value of the row and match it to what I need. (I searched for a string here "getValues", rather than getBackgroundColor, as stated in my question):

        var count = [];
    var count2 = [];

    for (var r in step) {

        var newrange = "F" + step[r][0] + ":F" + ((step[r][2] !== undefined) ? step[r][2] : "");

        var data2 = Sheet[S].getRange(newrange.toString()).getValues();

        for (var i in data2) {

            if (data2[i][0].indexOf("AN EXAMPLE VALUE") !== -1) {
                if (count[r] === undefined) {
                    count[r] = [];
                    count[r].push(step[r][1]);
                    count[r].push('1');
                    count[r].push(i);
                } else {
                    count[r].push(i);
                }
            }
            if (data2[i][0].indexOf("AN EXAMPLE VALUE 2") !== -1 || data2[i][0].indexOf("AN EXAMPLE VALUE 3") !== -1) {
                if (count2[r] === undefined) {
                    count2[r] = [];
                    count2[r].push(step[r][1]);
                    count2[r].push('2');
                    count2[r].push(i);
                } else {
                    count2[r].push(i);
                }
            }
        }
    }

Last I set a NEW Spreadsheet Sheet with these values (I suppose this could be on the same original spreadsheet, or maybe an ajax call, not sure if ajax works through the script console)

        for (var c in count) {
        var B = count[c][0];
        var C = count[c][1];
        count[c].splice(0, 2);
        var D = count[c].length;
        if (D > 0) {
            var srange = "A" + ctrak + ":D" + ctrak;
            srange = srange.toString();
            var valuestrak = [[C, A, D, B]];
            SheetTr.getRange(srange).setValues(valuestrak);

            ctrak++;
        }
    }
    for (var c in count2) {
        var B = count2[c][0];
        var C = count2[c][1];
        count2[c].splice(0, 2);
        var D = count2[c].length;
        if (D > 0) {
            var srange = "E" + ctrak2 + ":H" + ctrak2;
            srange = srange.toString();
            var valuestrak = [[C, A, D, B]];
            SheetTr.getRange(srange).setValues(valuestrak);

            ctrak2++;
        }
    }

Finally I pull the information from my server using the range I set it to:

$KEY_FILE_LOCATION = 'client_secret.json';
$client = new Google_Client();
$client->setApplicationName("xxxx");
$client->setAuthConfig($KEY_FILE_LOCATION);
$user_to_impersonate = '[email protected]';
$client->setSubject($user_to_impersonate);
$client->useApplicationDefaultCredentials();
$client->setScopes(['https://www.googleapis.com/auth/spreadsheets']);

$service = new Google_Service_Sheets($client);
$spreadsheetId = 'NEWXXXXXXSPREADSHEETXXXXXID';
$df = date('F');
echo $df . PHP_EOL;

$range = $df . '!A:D';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$vals = $response->getValues();
$values = [];
if (!empty($vals)) {
    if (is_array($vals)) {
        $values = $vals;
    } else {
        array_push($values, $vals);
    }
}

Google Spreadsheets Api V4 -> Get Background? PHP

1 Answers