0

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

ctrlbrk
  • 1,174
  • 2
  • 17
  • 27

1 Answers1

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 ] );
}
MonkeyZeus
  • 20,375
  • 4
  • 36
  • 77
  • Thank you, but the resulting array is only the header row itself -- with no data rows in the array. See https://pastebin.com/ECcAj6y3 – ctrlbrk Dec 11 '18 at 18:45
  • 1
    @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. – MonkeyZeus Dec 11 '18 at 18:47
  • 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 Dec 11 '18 at 18:49
  • For sake of completeness, here is the var_dump output from @MonkeyZeus's answer, so if anyone else is needing the same... https://pastebin.com/e1J7hzTk – ctrlbrk Dec 11 '18 at 18:51
  • 1
    @ctrlbrk Yes. [echo var_export($offersvalues);](http://php.net/manual/en/function.var-export.php) – MonkeyZeus Dec 11 '18 at 18:51
  • @ctrlbrk No problem. FYI pastebin's `var_dump()` seems quite terrible; I advise that you no longer use it especially when posting a question. – MonkeyZeus Dec 11 '18 at 19:06