0

I have used hours with google and trial and error but cant find an answer or good solution. My coding skills aren't that great so this might be obvious to gurus... Please somebody tell me what is going on :D

I am writing an order from webshop to google sheets. To have stuff in right order I create array $temparray which is then fed to google sheets.

For some reason the upload only works when I do this nasty implode-explode trick for inner array(s). (after hours of frustration I just started trying out random things and found out this helps)

Wtf is wrong with my array?

foreach ($_SESSION["ticketdata"] as $key => $value) {
    if ($_SESSION["debug"] == 1) {
        print $key." ";
    }
    $temparray[$key-1][0] = $_SESSION["ticketdata"][$key]["ticketid"];
    $temparray[$key-1][1] = $_SESSION["tickettype"];
    $temparray[$key-1][2] = $_SESSION["ticketdata"][$key]["ticketname-l"];
    $temparray[$key-1][3] = $_SESSION["ticketdata"][$key]["ticketname-f"];
    $temparray[$key-1][4] = $_SESSION["ticketdata"][$key]["ticketemail"];
    $temparray[$key-1][5] = $_SESSION["ticketdata"][$key]["ticketemployer"];
    $temparray[$key-1][6] = $_SESSION["ticketdata"][$key]["prews"];
    $temparray[$key-1][7] = $_SESSION["ticketdata"][$key]["ws26"];
    $temparray[$key-1][8] = $_SESSION["ticketdata"][$key]["ws27"];
    $temparray[$key-1][9] = $_SESSION["ticketdata"][$key]["diet-lactose"];
    $temparray[$key-1][10] = $_SESSION["ticketdata"][$key]["diet-gluten"];
    $temparray[$key-1][11] = $_SESSION["ticketdata"][$key]["diet-open"];
    $temparray[$key-1][12] = $_SESSION["orderid"];
    if ($key == 1) {
        $temparray[$key-1][13] = $_SESSION["totalprice"];
        $temparray[$key-1][14] = "";                        // placeholder
        $temparray[$key-1][15] = $_SESSION["bill-email"];
        $temparray[$key-1][16] = $_SESSION["bill-type"];
        $temparray[$key-1][17] = $_SESSION["bill-v-address"];
        $temparray[$key-1][18] = $_SESSION["bill-v-operator"];
        $temparray[$key-1][19] = $_SESSION["bill-additional"];
        $temparray[$key-1][20] = "";                        // placeholder
        $temparray[$key-1][21] = "";                        // placeholder
        $temparray[$key-1][22] = $_SESSION["customername-l"];
        $temparray[$key-1][23] = $_SESSION["customername-f"];
        $temparray[$key-1][24] = $_SESSION["customeremail"];
        $temparray[$key-1][25] = $_SESSION["customerphone"];
        $temparray[$key-1][26] = $_SESSION["customeremployer"];
        $temparray[$key-1][27] = date('Y-m-d H:i:s');
    }

    $temparraystr = implode("°§",$temparray[$key-1]); // Why do I need to do this?
    $temparray[$key-1] = explode("°§",$temparraystr); // mitä *ttua miksei toimi muuten????
}

// the google sheets part:
// getClient() copypasted from here: https://developers.google.com/sheets/api/quickstart/php
$client = getClient();
$service = new Google_Service_Sheets($client);    

$spreadsheetId = 'sheet id not shown on stack overflow :)';
$range = "tilaukset!A3:AB";

$valueRange = new Google_Service_Sheets_ValueRange();
$valueRange->setValues($temparray);

$conf = ["valueInputOption" => "RAW"];
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $conf);

Without the trickery I get this not-so-helpfull error message

 PHP Fatal error:  Uncaught exception 'Google_Service_Exception' with
 message '{\n  "error": {\n    "code": 400,\n    "message": "Invalid JSON
 payload received. Unknown name \\"1\\" at 'data.values': Cannot find
 field.\\nInvalid JSON payload received. Unknown name \\"2\\" at
 'data.values': Cannot find field.",\n    "errors": [\n      {\n
        "message": "Invalid JSON payload received. Unknown name \\"1\\"
 at 'data.values': Cannot find field.\\nInvalid JSON payload received.
 Unknown name \\"2\\" at 'data.values': Cannot find field.",\n 
       "domain": "global",\n        "reason": "badRequest"\n      }\n
    ],\n    "status": "INVALID_ARGUMENT"\n  }\n}\n'

print_r($temparray) output seems to be exactly identical with or without the implode-explode trick. This:

Array
(
    [0] => Array
        (
            [0] => SfGotLbD-1545531504-HZ2WGmpJ
            [1] => evy
            [2] => Secondname
            [3] => Firstname
            [4] => email@host
            [5] => employer
            [6] => 1
            [7] => ph
            [8] => ekg
            [9] => 
            [10] => 
            [11] => 
            [12] => 1545531504-HZ2WGmpJ
            [13] => 1050
            [14] => 
            [15] => email2@host
            [16] => verkkolasku
            [17] => 
            [18] => 
            [19] => 
            [20] => 
            [21] => 
            [22] => Secondname
            [23] => Firstname
            [24] => email@host
            [25] => 555 555555
            [26] => employer
            [27] => 2018-12-23 04:20:04
        )

)
TheMaster
  • 45,448
  • 6
  • 62
  • 85
jupet
  • 21
  • 6
  • 1
    1. Show how you're calling the api 2.Show your array – TheMaster Dec 22 '18 at 19:51
  • Edited the original to include more stuff. – jupet Dec 23 '18 at 02:21
  • Are you sending any invalid types? Valid types are null, bool, string and double. Try ``json_encode``? Also try adding a ``field`` config. Your error says unknown name at 1, but there's no 1 in your provided array sample. – TheMaster Dec 23 '18 at 09:58
  • With json_encode() the result seems to be same type of error: `[Mon Dec 24 05:47:47.522473 2018] [:error] [pid 1217] [client 1.46.143.59:27113] PHP Fatal error: Uncaught exception 'Google_Service_Exception' with message '{\n "error": {\n "code": 400,\n "message": "Invalid value at 'data.values' (type.googleapis.com/google.protobuf.ListValue), \\"[[\\"289O9hzC-1545623224-hGQIbobW\\",\\"l\\\\u00e4\\\\u00e4k\\\\u00e4ri\\", ...` Reading about types and trying to "force" define them soon. What is a field config? Google doen't want to reveal what that means... – jupet Dec 24 '18 at 03:55
  • If you can capture the outgoing payload from the client, you maybe able to diff both outputs and see what's different. AFAICT, Implode/explode just converts them all to strings. – TheMaster Dec 24 '18 at 09:13
  • The problem was invalid types, the numbers wanted to be int. Found out the difference with `var_dump();` which also lists the types. Now everything goes through `strval();` and everything seems to be working fine! As I anticipated, basic stuff. One learns more every day :) – jupet Dec 24 '18 at 16:10
  • Great! Consider adding it as a [good answer](https://stackoverflow.com/help/how-to-answer) below. – TheMaster Dec 24 '18 at 16:12

1 Answers1

2

The problem was wrong type of data in the array. It needs to be string.

With data going through strval() to make sure it is converted to string this works.

For data that is known to be string already this would not be mandatory.

Example:

$temparray[$key-1][0] = strval($_SESSION["ticketdata"][$key]["ticketid"]);
$temparray[$key-1][1] = strval($_SESSION["tickettype"]);
$temparray[$key-1][2] = strval($_SESSION["ticketdata"][$key]["ticketname-l"]);
$temparray[$key-1][3] = strval($_SESSION["ticketdata"][$key]["ticketname-f"]);
$temparray[$key-1][4] = strval($_SESSION["ticketdata"][$key]["ticketemail"]);
$temparray[$key-1][5] = strval($_SESSION["ticketdata"][$key]["ticketemployer"]);
jupet
  • 21
  • 6