Jack Trowbridge
Reputation: 3251
Inserting Large Array Via Google Sheets API (PHP)
I have a program that builds a large array. For this example, let's say it has 1 row. Then 78 columns. Here is a print_r of it:
Array ( [0] => Array ( [0] => BIZ ID [1] => B1 - A3 - Butt Seat Cushion [2] => BSC - Test 1 - Interests - Testing - Top 5, EU - WC [3] => Test 1 - Old age (24M) - Top 5, EU - [£19.54 / 1.593 BEP] - WC – Copy [4] => Video 2 - Thumbnail 1 [5] => [6] => CONVERSIONS [7] => 2689 [8] => 1960 [9] => [10] => [11] => [12] => [13] => [14] => [15] => [16] => [17] => [18] => [19] => 23.64 [20] => 157.59 [21] => 472.53 [22] => 92.87 [23] => 36.7255 [24] => 3.928511 [25] => 8.791372 [26] => 3.049461 [27] => 0.288293 [28] => 2.119747 [29] => 0.414737 [30] => 0.4925 [31] => 0.347647 [32] => 3.94 [33] => 2.149091 [34] => 7.88 [35] => 923 [36] => 446 [37] => 225 [38] => 117 [39] => 87 [40] => 78 [41] => 5 [42] => [43] => 48 [44] => 68 [45] => 6 [46] => 11 [47] => [48] => ABOVE_AVERAGE [49] => BELOW_AVERAGE_20 [50] => AVERAGE [51] => [52] => 1.371939 [53] => 3.214286 [54] => 0.375238 [55] => 2.44898 [56] => 48 [57] => 0.591 [58] => 0.606154 [59] => 4.728 [60] => 4.728 [61] => 7.88 [62] => 40 [63] => 39 [64] => 5 [65] => 5 [66] => 3 [67] => 993 [68] => 993 [69] => 6 [70] => [71] => 4 [72] => NAME [73] => 307978786963089 [74] => 23845484873330137 [75] => 23845818516690137 [76] => 23845818516750137 [77] => 2020-10-16 [78] => 2020-11-14 ) )
I would like to insert this into a Google Sheet. I have been able to do this with a simple array. However, I'm having trouble with this large array. I have got this code working fine:
// Initilize client
$this->client = new \Google_Client();
$this->client->setApplicationName('My PHP App');
$this->client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$this->client->setAccessType('offline');
$this->client->setAuthConfig("$authJSONFile");
// Initilize sheet
$this->sheets = new \Google_Service_Sheets($this->client);
// Sheet ID
$this->sheetID = $sheetID;
$values = [["1", "2"],["3", "4"]];
$body = new Google_Service_Sheets_ValueRange([
'values' => $values
]);
$params = [
'valueInputOption' => 'RAW'
];
$update_sheet = $this->sheets->spreadsheets_values->update($this->sheetID, ""Sheet1!A1:B2"", $body, $params);
It does what is expected. However, when I change $values to my large array I get this error:
Fatal error: Uncaught Google\Service\Exception: { "error": { "code": 400, "message": "Invalid JSON payload received. Unknown name \"0\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"1\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"2\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"3\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"4\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"5\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"6\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"7\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"8\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. Unknown name \"9\" at 'data.values[0]': Cannot find field.\nInvalid JSON payload received. in /home/zenkohealth/public_html/tools/FBAPIExtractor/_/composerPackages/vendor/google/apiclient/src/Http/REST.php on line 128
For the large array, I did make sure to change the selected range. I added 78 columns. I then changed the range to: Sheet1!A1:BZ1
I would then like to add new data to my large array. Then have it import into my Google Sheet. With everything working as expected. I would add data like this:
$largeArray[2] = ["Data","Data","Data","Data","Data","Data","Data","Data","Data"]
$largeArray[3] = ["Data","Data","Data","Data","Data","Data","Data","Data","Data"]
$largeArray[4] = ["Data","Data","Data","Data","Data","Data","Data","Data","Data"]
But, first I can't even get 1 row to work. I think I'm missing something or have a blind spot. I would appreciate any help!
Update
After taking a closer look at the error message. I see it's a JSON parsing issue. So I think showing how I make the array is important.
$FBDataRow = [];
// Loop through data
for($x = 0; $x < count($response["data"]); $x++) {
$FBDataRow[$x] = [
"$businessManagerName", // Start from 0
$response["data"][$x]["account_name"],
$response["data"][$x]["campaign_name"],
$response["data"][$x]["adset_name"],
$response["data"][$x]["ad_name"],
"", // Delivery status
$response["data"][$x]["objective"],
$response["data"][$x]["impressions"],
$response["data"][$x]["reach"],
"", // Budget
"", // Bid type
$response["data"][$x]["age"],
$response["data"][$x]["gender"],
$response["data"][$x]["country"],
$response["data"][$x]["region"],
$response["data"][$x]["publisher_platform"],
$response["data"][$x]["platform_position"],
$response["data"][$x]["impression_device"],
$response["data"][$x]["hourly_stats_aggregated_by_advertiser_time_zone"],
$response["data"][$x]["spend"],
"", // 20 - action_values.add_to_cart
"", // 21 action_values.initiate_checkout
"", // 22 - action_values.purchase
"", // 23 - estimated_profit
$response["data"][$x]["website_purchase_roas"][0]["value"],
$response["data"][$x]["cpm"],
$response["data"][$x]["ctr"],
$response["data"][$x]["cpc"],
$response["data"][$x]["website_ctr"][0]["value"],
$response["data"][$x]["cost_per_inline_link_click"],
"", // 30 - cost_per_action_type.landing_page_view
"", // 31 - cost_per_action_type.view_content:
"", // 32 - cost_per_action_type.add_to_cart
"", // 33 - cost_per_action_type.initiate_checkout
"", // 34 - cost_per_action_type.purchase
"", // 35 - actions.video_view
$response["data"][$x]["video_p25_watched_actions"][0]["value"],
$response["data"][$x]["video_p50_watched_actions"][0]["value"],
$response["data"][$x]["video_p75_watched_actions"][0]["value"],
$response["data"][$x]["video_p95_watched_actions"][0]["value"],
$response["data"][$x]["video_p100_watched_actions"][0]["value"],
$response["data"][$x]["video_avg_time_watched_actions"][0]["value"],
"", // 42 - actions.link_clicks
"", // 43 - actions.landing_page_view
"", // 44 - actions.view_content
"", // 45 - actions.add_to_cart
"", // 46 - actions.initiate_checkout
"", // 47 - actions.purchase
$response["data"][$x]["conversion_rate_ranking"],
$response["data"][$x]["engagement_rate_ranking"],
$response["data"][$x]["quality_ranking"],
$response["data"][$x]["qualifying_question_qualify_answer_rate"],
$response["data"][$x]["frequency"],
$response["data"][$x]["unique_ctr"],
$response["data"][$x]["cost_per_unique_click"],
$response["data"][$x]["unique_link_clicks_ctr"],
"", // 56 - unique_actions.link_click
"", // 57 - cost_per_unique_action_type.landing_page_view
"", // 58 - cost_per_unique_action_type.view_content:
"", // 59 - cost_per_unique_action_type.add_to_cart
"", // 60 - cost_per_unique_action_type.checkout
"", // 61 - cost_per_unique_action_type.purchase
"", // 62 - unique_actions.landing_page_view
"", // 63 - unique_actions.view_content
"", // 64 - unique_actions.add_to_cart
"", // 65 - unique_actions.checkout
"", // 66 - unique_actions.purchase
"", // 67 - actions.post_engagement
"", // 68 - actions.page_engagement
"", // 69 - actions.post_reaction
"", // 70 - actions.post_comment
"", // 71 - actions.onsite_conversion.post_save
"$businessManagerID",
$response["data"][$x]["account_id"],
$response["data"][$x]["campaign_id"],
$response["data"][$x]["adset_id"],
$response["data"][$x]["ad_id"],
$response["data"][$x]["date_start"],
$response["data"][$x]["date_stop"]
];
Upvotes: 0
Views: 950
Answers (1)
Jack Trowbridge
Reputation: 3251
I've solved it! :)
I decided to JSON encode the array I use with Google Sheets.
I found that many values were marked as null. This was not seen via the typical print_r
0 [79]
0 : BIZ ID
1 : B1 - A3 - Butt Seat Cushion
2 : BSC - Test 1 - Interests - Testing - Top 5, EU - WC
3 : Test 1 - Old age (24M) - Top 5, EU - [£19.54 / 1.593 BEP] - WC – Copy
4 : Video 2 - Thumbnail 1
5 :
6 : CONVERSIONS
7 : 2689
8 : 1960
9 :
10 :
11 : null
12 : null
13 : null
14 : null
15 : null
16 : null
17 : null
18 : null
19 : 23.64
20 : 157.59
21 : 472.53
22 : 92.87
23 : 36.7255
24 : 3.928511
25 : 8.791372
26 : 3.049461
27 : 0.288293
28 : 2.119747
29 : 0.414737
30 : 0.4925
31 : 0.347647
32 : 3.94
33 : 2.149091
34 : 7.88
35 : 923
36 : 446
37 : 225
38 : 117
39 : 87
40 : 78
41 : 5
42 :
43 : 48
44 : 68
45 : 6
46 : 11
47 :
48 : ABOVE_AVERAGE
49 : BELOW_AVERAGE_20
50 : AVERAGE
51 : null
52 : 1.371939
53 : 3.214286
54 : 0.375238
55 : 2.44898
56 : 48
57 : 0.591
58 : 0.606154
59 : 4.728
60 : 4.728
61 : 7.88
62 : 40
63 : 39
64 : 5
65 : 5
66 : 3
67 : 993
68 : 993
69 : 6
70 :
71 : 4
72 : NAME
73 : 307978786963089
74 : 23845484873330137
75 : 23845818516690137
76 : 23845818516750137
77 : 2020-10-16
78 : 2020-11-14
So I decided to run a loop after making the array to replace all null with ""
// Remove all the "null" values from rows
foreach ($FBDataRow as $key => $value) {
foreach($FBDataRow[$key] as $key2 => $value2) {
if(is_null($value2))
$FBDataRow[$key][$key2] = "";
}
}
This seemed to do the trick. The data imports perfectly :)
Upvotes: 2
Related Questions
- Add multiple rows to Google Spreadsheet via API
- How to write data to Google Sheet using PHP
- PHP array oddity when adding rows to google sheets
- Accessing Google Sheets API array in PHP with named array keys
- Creating a sheet with more than 1000 rows
- Google Sheets API V4 PHP - inserting data 400 error
- Google spreadsheet API how to insert new row
- Google Sheets API - Insert a row with PHP
- Inserting rows in existing Google Spreadsheet with pure PHP
- Insert hundreds of rows into Google spreadsheet with Zend Gdata?