-
-
Notifications
You must be signed in to change notification settings - Fork 15
/
Create.ino
209 lines (171 loc) · 8.77 KB
/
Create.ino
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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
/**
* Created by K. Suwatchai (Mobizt)
*
* Email: suwatchai@outlook.com
*
* Github: https://github.com/mobizt
*
* Copyright (c) 2023 mobizt
*
*/
// This example shows how to create the spreadsheet.
#include <Arduino.h>
#if defined(ESP32) || defined(ARDUINO_RASPBERRY_PI_PICO_W)
#include <WiFi.h>
#elif defined(ESP8266)
#include <ESP8266WiFi.h>
#elif __has_include(<WiFiNINA.h>)
#include <WiFiNINA.h>
#elif __has_include(<WiFi101.h>)
#include <WiFi101.h>
#elif __has_include(<WiFiS3.h>)
#include <WiFiS3.h>
#endif
// For SD/SD_MMC mounting helper
#include <GS_SDHelper.h>
#define WIFI_SSID "WIFI_AP"
#define WIFI_PASSWORD "WIFI_PASSWORD"
// For how to create Service Account and how to use the library, go to https://github.com/mobizt/ESP-Google-Sheet-Client
#define PROJECT_ID "PROJECT_ID"
// Service Account's client email
#define CLIENT_EMAIL "CLIENT_EMAIL"
// Your email to share access to spreadsheet
#define USER_EMAIL "USER_EMAIL"
// Service Account's private key
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----XXXXXXXXXXXX-----END PRIVATE KEY-----\n";
/**
const char rootCACert[] PROGMEM = "-----BEGIN CERTIFICATE-----\n"
"MIIFVzCCAz+gAwIBAgINAgPlk28xsBNJiGuiFzANBgkqhkiG9w0BAQwFADBHMQsw\n"
"CQYDVQQGEwJVUzEiMCAGA1UEChMZR29vZ2xlIFRydXN0IFNlcnZpY2VzIExMQzEU\n"
"MBIGA1UEAxMLR1RTIFJvb3QgUjEwHhcNMTYwNjIyMDAwMDAwWhcNMzYwNjIyMDAw\n"
"MDAwWjBHMQswCQYDVQQGEwJVUzEiMCAGA1UEChMZR29vZ2xlIFRydXN0IFNlcnZp\n"
"Y2VzIExMQzEUMBIGA1UEAxMLR1RTIFJvb3QgUjEwggIiMA0GCSqGSIb3DQEBAQUA\n"
"A4ICDwAwggIKAoICAQC2EQKLHuOhd5s73L+UPreVp0A8of2C+X0yBoJx9vaMf/vo\n"
"27xqLpeXo4xL+Sv2sfnOhB2x+cWX3u+58qPpvBKJXqeqUqv4IyfLpLGcY9vXmX7w\n"
"Cl7raKb0xlpHDU0QM+NOsROjyBhsS+z8CZDfnWQpJSMHobTSPS5g4M/SCYe7zUjw\n"
"TcLCeoiKu7rPWRnWr4+wB7CeMfGCwcDfLqZtbBkOtdh+JhpFAz2weaSUKK0Pfybl\n"
"qAj+lug8aJRT7oM6iCsVlgmy4HqMLnXWnOunVmSPlk9orj2XwoSPwLxAwAtcvfaH\n"
"szVsrBhQf4TgTM2S0yDpM7xSma8ytSmzJSq0SPly4cpk9+aCEI3oncKKiPo4Zor8\n"
"Y/kB+Xj9e1x3+naH+uzfsQ55lVe0vSbv1gHR6xYKu44LtcXFilWr06zqkUspzBmk\n"
"MiVOKvFlRNACzqrOSbTqn3yDsEB750Orp2yjj32JgfpMpf/VjsPOS+C12LOORc92\n"
"wO1AK/1TD7Cn1TsNsYqiA94xrcx36m97PtbfkSIS5r762DL8EGMUUXLeXdYWk70p\n"
"aDPvOmbsB4om3xPXV2V4J95eSRQAogB/mqghtqmxlbCluQ0WEdrHbEg8QOB+DVrN\n"
"VjzRlwW5y0vtOUucxD/SVRNuJLDWcfr0wbrM7Rv1/oFB2ACYPTrIrnqYNxgFlQID\n"
"AQABo0IwQDAOBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4E\n"
"FgQU5K8rJnEaK0gnhS9SZizv8IkTcT4wDQYJKoZIhvcNAQEMBQADggIBAJ+qQibb\n"
"C5u+/x6Wki4+omVKapi6Ist9wTrYggoGxval3sBOh2Z5ofmmWJyq+bXmYOfg6LEe\n"
"QkEzCzc9zolwFcq1JKjPa7XSQCGYzyI0zzvFIoTgxQ6KfF2I5DUkzps+GlQebtuy\n"
"h6f88/qBVRRiClmpIgUxPoLW7ttXNLwzldMXG+gnoot7TiYaelpkttGsN/H9oPM4\n"
"7HLwEXWdyzRSjeZ2axfG34arJ45JK3VmgRAhpuo+9K4l/3wV3s6MJT/KYnAK9y8J\n"
"ZgfIPxz88NtFMN9iiMG1D53Dn0reWVlHxYciNuaCp+0KueIHoI17eko8cdLiA6Ef\n"
"MgfdG+RCzgwARWGAtQsgWSl4vflVy2PFPEz0tv/bal8xa5meLMFrUKTX5hgUvYU/\n"
"Z6tGn6D/Qqc6f1zLXbBwHSs09dR2CQzreExZBfMzQsNhFRAbd03OIozUhfJFfbdT\n"
"6u9AWpQKXCBfTkBdYiJ23//OYb2MI3jSNwLgjt7RETeJ9r/tSQdirpLsQBqvFAnZ\n"
"0E6yove+7u7Y/9waLd64NnHi/Hm3lCXRSHNboTXns5lndcEZOitHTtNCjv0xyBZm\n"
"2tIMPNuzjsmhDYAPexZ3FL//2wmUspO8IFgV6dtxQ/PeEMMA3KgqlbbC1j+Qa3bb\n"
"bP6MvPJwNQzcmRk13NfIRmPVNnGuV/u3gm3c\n"
"-----END CERTIFICATE-----\n";
*/
bool taskComplete = false;
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
WiFiMulti multi;
#endif
void tokenStatusCallback(TokenInfo info);
void setup()
{
Serial.begin(115200);
Serial.println();
Serial.println();
GSheet.printf("ESP Google Sheet Client v%s\n\n", ESP_GOOGLE_SHEET_CLIENT_VERSION);
#if defined(ESP32) || defined(ESP8266)
WiFi.setAutoReconnect(true);
#endif
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
multi.addAP(WIFI_SSID, WIFI_PASSWORD);
multi.run();
#else
WiFi.begin(WIFI_SSID, WIFI_PASSWORD);
#endif
Serial.print("Connecting to Wi-Fi");
unsigned long ms = millis();
while (WiFi.status() != WL_CONNECTED)
{
Serial.print(".");
delay(300);
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
if (millis() - ms > 10000)
break;
#endif
}
Serial.println();
Serial.print("Connected with IP: ");
Serial.println(WiFi.localIP());
Serial.println();
// In case SD/SD_MMC storage file access, mount the SD/SD_MMC card.
// SD_Card_Mounting(); // See src/GS_SDHelper.h
// GSheet.setCert(rootCACert); // or GSheet.setCertFile("path/to/certificate/file.pem", esp_google_sheet_file_storage_type_flash /* or esp_google_sheet_file_storage_type_sd */);
// Set the callback for Google API access token generation status (for debug only)
GSheet.setTokenCallback(tokenStatusCallback);
// The WiFi credentials are required for Pico W
// due to it does not have reconnect feature.
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
GSheet.clearAP();
GSheet.addAP(WIFI_SSID, WIFI_PASSWORD);
#endif
// Set the seconds to refresh the auth token before expire (60 to 3540, default is 300 seconds)
GSheet.setPrerefreshSeconds(10 * 60);
// Begin the access token generation for Google API authentication
GSheet.begin(CLIENT_EMAIL, PROJECT_ID, PRIVATE_KEY);
// Or begin with the Service Account JSON file
// GSheet.begin("path/to/serviceaccount/json/file", esp_google_sheet_file_storage_type_flash /* or esp_google_sheet_file_storage_type_sd */);
}
void loop()
{
// Call ready() repeatedly in loop for authentication checking and processing
bool ready = GSheet.ready();
if (ready && !taskComplete)
{
// For basic FirebaseJson usage example, see examples/FirebaseJson/Create_Edit_Parse/Create_Edit_Parse.ino
FirebaseJson response;
// Instead of using FirebaseJson for response, you can use String for response to the functions
// especially in low memory device that deserializing large JSON response may be failed as in ESP8266
Serial.println("\nCreate spreadsheet...");
Serial.println("------------------------");
FirebaseJson spreadsheet;
spreadsheet.set("properties/title", "Sheet1");
spreadsheet.set("sheets/properties/title", "Sheet1");
spreadsheet.set("sheets/properties/sheetId", 1);
spreadsheet.set("sheets/properties/sheetType", "GRID");
spreadsheet.set("sheets/properties/sheetType", "GRID");
spreadsheet.set("sheets/properties/gridProperties/rowCount", 20);
spreadsheet.set("sheets/properties/gridProperties/columnCount", 10);
spreadsheet.set("sheets/developerMetadata/[0]/metadataValue", "test_value");
spreadsheet.set("sheets/developerMetadata/[0]/metadataKey", "test_yey");
spreadsheet.set("sheets/developerMetadata/[0]/visibility", "DOCUMENT");
// For Google Sheet API ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create
bool success = GSheet.create(&response /* returned response */, &spreadsheet /* spreadsheet object */, USER_EMAIL /* your email that this spreadsheet shared to */);
if (success)
response.toString(Serial, true);
else
Serial.println(GSheet.errorReason());
Serial.println();
#if defined(ESP32) || defined(ESP8266)
Serial.println(ESP.getFreeHeap());
#elif defined(PICO_RP2040)
Serial.println(rp2040.getFreeHeap());
#endif
taskComplete = true;
}
}
void tokenStatusCallback(TokenInfo info)
{
if (info.status == token_status_error)
{
GSheet.printf("Token info: type = %s, status = %s\n", GSheet.getTokenType(info).c_str(), GSheet.getTokenStatus(info).c_str());
GSheet.printf("Token error: %s\n", GSheet.getTokenError(info).c_str());
}
else
{
GSheet.printf("Token info: type = %s, status = %s\n", GSheet.getTokenType(info).c_str(), GSheet.getTokenStatus(info).c_str());
}
}