converting a Google-Sheet fed json string into an array with interpolated key default values via php script

Question

I have a json output from a regular Google Sheet Doc (following this guide) that outputs exactly this format

{
  "range": "json!A1:Y1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "nome",
      "cognome",
      "salary",
      "assunzione",
      "location",
      "extension"
    ],
    [
      "james",
      "di marcio",
      "$85,675",
      "09/12/2009",
      "San Francisco",
      "5384"
    ],
    [
      "marco",
      "giaccanti",
      "$54,560",
      "06/06/2020",
      "roma",
      "5385"
    ]
  ]
}

and needs to be converted into this very format

{
  "data": [
    {
      "id": "1",
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": "2011/04/25",
      "office": "Edinburgh",
      "extn": "5421"
    },
    {
      "id": "2",
      "name": "Frank Major",
      "position": "top manager",
      "salary": "$320,800",
      "start_date": "2011/06/20",
      "office": "San Francisco",
      "extn": "5221"
    }
  ]
}

in order to be usable within this Datatables project.

The best I could come up with was this solution, which of course doesn't fit the required pattern AND only applies to the first of the arrays nested inside "values", while it would need to convert them all:

 <?php
$json   = file_get_contents('https://sheets.googleapis.com/v4/spreadsheets/[my_sheet_id]/values/json?key=[my_api_key]');
$objs   = json_decode($json, false);
$codes  = $objs->values[0];
$names  = $objs->values[1];


foreach( $codes as $index => $code ) {
   echo  $code. ': ' . $names[$index]." ";
}
?>

nome: james cognome: di marcio salary: $85,675 assunzione: 09/12/2009 location: San Francisco extension: 5384

The idea was to set automatically the first array values as keys for the looping values, but of course the code is not enough ..

Any suggestion?

A side consideration: Datatables doesn't care which kind of file the table is fed. You may choose a .txt file as well as a .php file or I think may be even a .js file.
May be a little naif, I thought that .php was the best solution because -I thought- the work of processing is done server-side instead of client-side, resulting may be in a faster process. Is this the case?

ps: for duplicate seekers, I checked different sources before posting and none of them really fit my case

Mohsen Nazari · Accepted Answer · 2021-07-15T23:48:37.353

Actually it is easier than you think,

$originalArray = json_decode($json, true);

$keys = ["id", "name", "position", "salary", "start_date", "office", "extn"];
$rows = array_slice($originalArray['values'], 1);

$data = [];
foreach($rows as $i => $row) {
    array_unshift($row, (string)($i + 1));
    
    $data[] = array_combine($keys, $row);
}

$output = json_encode(["data" => $data]);

The process is first, we decode the json to get corresponding associative array:

$originalArray = json_decode($json, true);

Then we define our keys that we are gonna assign to every record:

$keys = ["id", "name", "position", "salary", "start_date", "office", "extn"];

Then we extract the actual records containing data which starts from index 1 of key values inside the $originalArray.

$rows = array_slice($originalArray['values'], 1);

Now our records are all values like below:

    [
      "james",
      "di marcio",
      "$85,675",
      "09/12/2009",
      "San Francisco",
      "5384"
    ]

We need to combine the values above with the corresponding $keys we had before. We loop over all the records $rows and assign them the keys. We use the function array_combine of PHP which accepts two array inputs with same length, first is used as keys and second as values and then combines them.

But we have to note that our value records don't have id values by themselves so we have to merge the values with an extra index-like field for id. So we use array_unshift to add index as the first element of array $row (we also convert it to string). So the code becomes:

$data = [];
foreach($rows as $i => $row) {
    array_unshift($row, (string)($i + 1));
    
    $data[] = array_combine($keys, $row);
}

Note: array_unshift accepts the array $row as reference type and changes it.

The output now is like:

[
    id=> "1",
    name=> "james",
    position=> "di marcio",
    salary=> "$85,675",
    start_date=> "09/12/2009",
    office=> "San Francisco",
    extn=> "5384"
]

Finally we need to encode this associate array to json and return it, also we have to make a key for it called data, so:

$output = json_encode(["data" => $data]);

The output becomes:

{
    "data": [
        {
            "id": "1",
            "name": "james",
            "position": "di marcio",
            "salary": "$85,675",
            "start_date": "09/12/2009",
            "office": "San Francisco",
            "extn": "5384"
        },
        {
            "id": "2",
            "name": "marco",
            "position": "giaccanti",
            "salary": "$54,560",
            "start_date": "06/06/2020",
            "office": "roma",
            "extn": "5385"
        }
    ]
}

thanks. There's one point though: if the original GSheet presents any **empty cells** your code will result in one or more missing values in the corresponding ```values[n]``` array, generating a false output in php, like: ```{"data":[false,{"id":"2"..}]}```. Can we fix that? Ideal would be that I could be able to set a given number before combining the arrays and instruct: ```you must produce exactly "n" occurrences. If you don't find any correspondence, just leave blank```.I am surprised that such an elementary thing is missing after the same question has been asked again and again, am I not? — John Galassi, Jul 16 '21 at 13:33

score 2 · Answer 2 · answered Jul 15 '21 at 21:48

$json   = file_get_contents('https://sheets.googleapis.com/v4/spreadsheets/[my_sheet_id]/values/json?key=[my_api_key]');
$objs  = json_decode($json, false);

// Set the header
$codes = $objs->values[0];

// Unset header values
unset($objs->values[0]);

// Loop the rows
foreach( $objs->values as $key => $jsonRow ) {
    // Create a line to add with id field
    $lineToAdd['id'] = $key;
    // Add values from the row
    foreach ( $codes as $codeKey => $code ) {
        $lineToAdd[$code] = $jsonRow[$codeKey];
    }
   $data[] = $lineToAdd;
}

score 2 · Answer 3 · answered Jul 15 '21 at 21:50

2

Here is a script which performs the basic conversion from the array of arrays to the array of objects:

let sourceFormat = {
  "range": "json!A1:Y1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "nome",
      "cognome",
      "salary",
      "assunzione",
      "location",
      "extension"
    ],
    [
      "james",
      "di marcio",
      "$85,675",
      "09/12/2009",
      "San Francisco",
      "5384"
    ],
    [
      "marco",
      "giaccanti",
      "$54,560",
      "06/06/2020",
      "roma",
      "5385"
    ]
  ]
};

let targetFormat = [];

let headings = sourceFormat.values.slice(0, 1)[0];

sourceFormat.values.slice(1).forEach((row) => { 
  let rowObject = {};
  row.forEach((item, index) => {
    var name = headings[index];
    rowObject[name] = item;
  } )
  targetFormat.push ( rowObject );
} )

console.log( { "data" : targetFormat } );

You can add console.log() statements to see what is happening at each step, but here is a walkthrough:

First , we treat the headings as a separate array:

sourceFormat.values.slice(0, 1)[0]

And then taking the remainder of the array of arrays as your data rows:

sourceFormat.values.slice(1)

We then iterate over each of these arrays, and build one object for each one:

sourceFormat.values.slice(1).forEach((row) => { ... } )

We use the index location from each inner array to look up the related heading name:

var name = headings[index];

We then start adding entries into our (initially empty let rowObject = {};) row object:

rowObject[name] = item;

The above line is what builds up each name/value pair in each object.

We push each new object onto a new array:

targetFormat.push ( rowObject );

Finally, we wrap everything up in one final outer object:

{ "data" : targetFormat }

The result is this:

{
  "data": [
    {
      "nome": "james",
      "cognome": "di marcio",
      "salary": "$85,675",
      "assunzione": "09/12/2009",
      "location": "San Francisco",
      "extension": "5384"
    },
    {
      "nome": "marco",
      "cognome": "giaccanti",
      "salary": "$54,560",
      "assunzione": "06/06/2020",
      "location": "roma",
      "extension": "5385"
    }
  ]
}

answered Jul 15 '21 at 21:50

andrewJames

19,570
8
19
51

He needs custom key names in the output, also an extra `id` too! – Mohsen Nazari Jul 15 '21 at 22:19
1

@MohsenNazari - I understand your point - the question, as written, does suggest what you are saying. But I also believe this is a question about _structure_, more than a question about _content_. See [this](https://stackoverflow.com/questions/68351512/proper-format-and-commands-for-google-sheet-json-fed-datatables) for some additional background (which is not covered in this question). The best way to know for sure would be for this point to be clarified by the asker, of course. – andrewJames Jul 15 '21 at 23:11
I get your point, you are right. And he keeps asking the same question again and again :)) – Mohsen Nazari Jul 15 '21 at 23:15
1

@MohsenNazari I like your answer, by the way (+1) - maybe you can add a couple of sentences explaining the key points? It is more concise than my approach. More answers/approaches is better than fewer. – andrewJames Jul 15 '21 at 23:17
Just made the update about details, just because you asked <3 – Mohsen Nazari Jul 15 '21 at 23:39
thanks both for your replies. As of now, I am more inclined to choose the php solution instead of js but I wanted to ask to @andrewjames: is there a way to store the json string from google into a variable automatically? I mean an equivalent to the ```php file_get_contents()```, so that, like it happens with the php script, I can automatically grab the content from the google output json string and store it into a variable right away. this is what I mean: https://stackoverflow.com/a/10693572/6589922 – John Galassi Jul 16 '21 at 09:06
1

Good question - sorry, I don't know how to do that. I am sure you will get a good answer if you ask that specific question. – andrewJames Jul 16 '21 at 12:00
@andrewjames thanks anyways. I guess I could always find an answer, however in this case my impression is that php provides a more agile solution. For this reason I decided to go with it and mark correct Nazari's answer, even if he tried to make fun of me some comments above. (And BTW php is also what my original example already provided for the case ```mysql source```, even if I can't recall where I took it).Thanks for your contribution and for your explanation which was very helpful anyways,I hope I can find your support in new JS questions in the future, because I still have a lot to ask :) – John Galassi Jul 17 '21 at 06:34

converting a Google-Sheet fed json string into an array with interpolated key default values via php script

3 Answers3