CODEWITHSUNDEEP
phparraysgoogle-apigoogle-api-php-client
ctrlbrk
ctrlbrk

Reputation: 1194

Accessing Google Sheets API array in PHP with named array keys

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

Upvotes: 0

Views: 1158

Answers (1)

MonkeyZeus
MonkeyZeus

Reputation: 20737

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

Upvotes: 2

Related Questions