Accessing Google Sheets API array in PHP with named array keys

Question

Using the following basic Google Sheets API code:

$client = getClient();
$service = new Google_Service_Sheets($client);
$spreadsheetId = 'xxxxxxxxxx';

$offersrange = 'Leads';
$offersresponse = $service->spreadsheets_values->get($spreadsheetId, $offersrange);
$offersvalues = $offersresponse->getValues();

var_dump($offersvalues);die;

I want to be able to access the array using named keys, like $array[date] and $array[vendor]. But here is the var_dump output, and I'm struggling to understand how to get this into an array where I can use a named key.

Below is var_dump from a 3-row sheet, row 1 is header, row 2 and 3 are sample data.

array(3) { [0]=> array(7) { [0]=> string(4) "date" [1]=> string(6) "vendor" [2]=> string(6) "userid" [3]=> string(8) "username" [4]=> string(5) "email" [5]=> string(6) "status" [6]=> string(11) "description" } [1]=> array(7) { [0]=> string(8) "12/11/18" [1]=> string(2) "v1" [2]=> string(1) "1" [3]=> string(2) "u1" [4]=> string(2) "e1" [5]=> string(1) "0" [6]=> string(16) "test description" } [2]=> array(7) { [0]=> string(8) "12/11/18" [1]=> string(2) "v2" [2]=> string(1) "2" [3]=> string(2) "u2" [4]=> string(2) "e2" [5]=> string(1) "1" [6]=> string(11) "another one" } }

Formatted var_dump:

(array) [3 elements]
0: 
(array) [7 elements]
0: (string) "date"
1: (string) "vendor"
2: (string) "userid"
3: (string) "username"
4: (string) "email"
5: (string) "status"
6: (string) "description"
1: 
(array) [7 elements]
0: (string) "12/11/18"
1: (string) "v1"
2: (string) "1"
3: (string) "u1"
4: (string) "e1"
5: (string) "0"
6: (string) "test description"
2: 
(array) [7 elements]
0: (string) "12/11/18"
1: (string) "v2"
2: (string) "2"
3: (string) "u2"
4: (string) "e2"
5: (string) "1"
6: (string) "another one"

Ultimately, my goal is to be able to reference variables such as $row['status'] and $row['date'] in my code.

I need the names to be "dynamic" based on the source spreadsheet, so that if the spreadsheet columns are changed in the future, the array names are still consistent (date is always date for example, even if it's no longer the first column).

that is the var_dump, there are 3 rows in the spreadsheet - header and two sample rows of data. — ctrlbrk
– ctrlbrk, Commented Dec 11, 2018 at 18:28
Nevermind, I get it now. Your var_dump() format is not what I am used to. for future reference echo '<pre>'; var_dump($offersvalues); would help to avoid confusion. — MonkeyZeus
– MonkeyZeus, Commented Dec 11, 2018 at 18:37

MonkeyZeus · Accepted Answer · 2018-12-11 18:46:01Z

2

You can choose to either use the first row as a reference or loop through all the results and rename the keys yourself:

Use first row as reference point:

$cols = array_shift( $offersvalues );
$cols = array_flip( $cols );
$row1_date = $offersvalues[ 0 ][ $cols[ 'date' ] ];

Rename the cols yourself:

$cols = array_shift( $offersvalues );
$new_offersvalues = array();

foreach( $offersvalues as $k=>$v )
{
    $new_offersvalues[ $k ] = array();

    foreach( $v as $k2=>$v2 )
    {
        $new_offersvalues[ $k ][ $cols[ $k2 ] ] = $v2;
    }

    unset( $offersvalues[ $k ] );
}

edited Dec 11, 2018 at 18:46

answered Dec 11, 2018 at 18:30

MonkeyZeus

20.8k4 gold badges41 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ctrlbrk Over a year ago

Thank you, but the resulting array is only the header row itself -- with no data rows in the array. See pastebin.com/ECcAj6y3

MonkeyZeus Over a year ago

@ctrlbrk My bad, I had some typos. You should consider providing a sample data array which I can easily copy+paste and test my code.

ctrlbrk Over a year ago

thank you, the revised code works. For future reference, is there an easy way for me to dump sample data from PHP array to paste into stackoverflow? I was providing the var_dump because I wasn't sure how else to express it easily, without giving an actual Google Sheets id.

ctrlbrk Over a year ago

For sake of completeness, here is the var_dump output from @MonkeyZeus's answer, so if anyone else is needing the same... pastebin.com/e1J7hzTk

MonkeyZeus Over a year ago

@ctrlbrk Yes. echo var_export($offersvalues);

|

Collectives™ on Stack Overflow

Accessing Google Sheets API array in PHP with named array keys

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related