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

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"
        }
    ]
}

$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;
}

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"
    }
  ]
}