1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Enquiry extends CI_Controller
{
private function getClient()
{
$client = new Google_Client();
$client->setApplicationName('Google Sheets API PHP Quickstart');
$client->setScopes(Google_Service_Sheets::SPREADSHEETS);
$client->setAuthConfig(FCPATH.'quickstart-1561710979346-cf1dda0aa1df.json');
$client->setAccessType('offline');
$client->setPrompt('select_account consent');
// Load previously authorized token from a file, if it exists.
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
$tokenPath = FCPATH.'token.json';
if (file_exists($tokenPath)) {
$accessToken = json_decode(file_get_contents($tokenPath), true);
$client->setAccessToken($accessToken);
}
// If there is no previous token or it's expired.
if ($client->isAccessTokenExpired()) {
// Refresh the token if possible, else fetch a new one.
if ($client->getRefreshToken()) {
$client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
} else {
// Request authorization from the user.
$authUrl = $client->createAuthUrl();
printf("Open the following link in your browser:\n%s\n", $authUrl);
print 'Enter verification code: ';
$authCode = trim(fgets(STDIN));
// Exchange authorization code for an access token.
$accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
$client->setAccessToken($accessToken);
// Check to see if there was an error.
if (array_key_exists('error', $accessToken)) {
throw new Exception(join(', ', $accessToken));
}
}
// Save the token to a file.
if (!file_exists(dirname($tokenPath))) {
mkdir(dirname($tokenPath), 0700, true);
}
file_put_contents($tokenPath, json_encode($client->getAccessToken()));
}
return $client;
}
public function index()
{
$this->load->vars('rows',Enquiry_model::all());
$this->load->view("main/enquiry_list");
}
public function sync()
{
// Get the API client and construct the service object.
$client = $this->getClient();
$service = new Google_Service_Sheets($client);
/*
* With the Google_Client we can get a Google_Service_Sheets service object to interact with sheets
*/
$sheets = new Google_Service_Sheets($client);
/*
* To read data from a sheet we need the spreadsheet ID and the range of data we want to retrieve.
* Range is defined using A1 notation, see https://developers.google.com/sheets/api/guides/concepts#a1_notation
*/
$data = [];
// The first row contains the column titles, so lets start pulling data from row 2
$currentRow = 2;
// The range of A2:H will get columns A through H and all rows starting from row 2
$spreadsheetId = '1LV0hOrNabGjIRzSCZVYjjNjF82plRspWQl2KbSKJ90M';
//write
$endRow = 20; // Keep spreadsheet header
$startCol = 'B';
$endCol = 'H';
$data = array();
for($row=$currentRow;$row<=$endRow;$row++){
$range = $startCol.$row.':'.$endCol;
$values = array();
$value = array();
foreach(range($startCol, $endCol) as $col) {
$value[] = $col.$row;
}
array_push($values,$value);
// print_r($values);exit;
if ( ! empty($values)) {
$data[] = new Google_Service_Sheets_ValueRange(
array( 'range' => $range, 'values' => $values )
);
//last update
$updateRange = 'I'.$row;
$updateBody = new Google_Service_Sheets_ValueRange([
'range' => $updateRange,
'majorDimension' => 'ROWS',
'values' => ['values' => date('Y-m-d H:i:s')],
]);
$sheets->spreadsheets_values->update(
$spreadsheetId,
$updateRange,
$updateBody,
['valueInputOption' => 'USER_ENTERED']
);
}
}
$requestBody = new Google_Service_Sheets_BatchUpdateValuesRequest([
'valueInputOption'=>'USER_ENTERED',
'data'=> $data,
]);
$response = $sheets->spreadsheets_values->batchUpdate($spreadsheetId, $requestBody);
// print_r($value);
//read
$range = 'A2:H';
$rows = $sheets->spreadsheets_values->get($spreadsheetId, $range, ['majorDimension' => 'ROWS']);
if (isset($rows['values'])) {
foreach ($rows['values'] as $row) {
/*
* If first column is empty, consider it an empty row and skip (this is just for example)
*/
if (empty($row[0])) {
break;
}
print_r($row);
}
}
}
}
|