ESP8266 Spreadsheet update

[MeowCattoNiP]

Hi, in the example code, there's a comment stating to use string instead of FirebaseJSON. I am not sure how to implement that.

` 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

    FirebaseJson valueRange;

    valueRange.add("range", "Sheet1!A1:B5");
    valueRange.add("majorDimension", "ROWS");
    valueRange.set("values/[0]/[0]", "1"); // row 1/ column 1
    valueRange.set("values/[1]/[0]", "2"); // row 2/ column 1
    valueRange.set("values/[2]/[0]", "3"); // row 3/ column 1
    valueRange.set("values/[3]/[0]", "4"); // row 4/ column 1
    valueRange.set("values/[4]/[0]", "5"); // row 5/ column 1

    // With formula
    valueRange.set("values/[0]/[1]", "=SUM(A1:A5)");     // row 1/ column 2
    valueRange.set("values/[1]/[1]", "=COUNT(A1:A5)");   // row 2/ column 2
    valueRange.set("values/[2]/[1]", "=AVERAGE(A1:A5)"); // row 3/ column 2

    // For Google Sheet API ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

    bool success = GSheet.values.update(&response /* returned response */, "<spreadsheetId>" /* spreadsheet Id to update */, "Sheet1!A1:B5" /* range to update */, &valueRange /* data to update */);
    response.toString(Serial, true);`

[mobizt]

Pass the String pointer to the function like this.

String response;

GSheet.values.update(&response, ...

[MeowCattoNiP]

I still cant figure out how to use the update function. From my understanding, the
GSheet.values.update(&response, PROJECT_ID, range, &valueRange);
response variable has been set to String as you pointed out, however the valueRange.add or valueRange.set seems to give me errors saying that add and set is not a named member. Also the response.toString(Serial, true); is giving errors as well. I am not sure how to proceed at this point. My idea of using this function to update data of SSID, BSSID and RSSI to the spreadsheet if that helps you visualise my intent.

[mobizt]

I don't understand what error you mentioned where it is from. You should post the response that printed on the Serial.

[MeowCattoNiP]

Here's the error msg i got:

E:\FYP Deg\Updating\Updating.ino: In function 'void loop()':
E:\FYP Deg\Updating\Updating.ino:121:20: error: 'class String' has no member named 'add'
121 | valueRange.add("range", "Sheet1!A1:B5");
| ^~~
E:\FYP Deg\Updating\Updating.ino:122:20: error: 'class String' has no member named 'add'
122 | valueRange.add("majorDimension", "ROWS");
| ^~~
E:\FYP Deg\Updating\Updating.ino:123:20: error: 'class String' has no member named 'set'
123 | valueRange.set("values/[0]/[0]", "1"); // row 1/ column 1
| ^~~
E:\FYP Deg\Updating\Updating.ino:136:202: error: no matching function for call to 'GSheet_Values::update(String*, const char [18], const char [13], String*)'
136 | bool success = GSheet.values.update(&response /* returned response /, "" / spreadsheet Id to update /, "Sheet1!A1:B5" / range to update /, &valueRange / data to update /);
| ^
In file included from E:\FYP Deg\Updating\Updating.ino:17:
c:\Users\hmski\Documents\Arduino\libraries\ESP-Google-Sheet-Client-master\src/ESP_Google_Sheet_Client.h:632:10: note: candidate: 'template<class T1, class T2, class T3, class T4, class T5, class T6> bool GSheet_Values::update(FirebaseJson, T1, T2, FirebaseJson*, T3, T4, T5, T6)'
632 | bool update(FirebaseJson response, T1 spreadsheetId, T2 range, FirebaseJson valueRange, T3 valueInputOption = "USER_ENTERED", T4 includeValuesInResponse = "", T5 responseValueRenderOption = "", T6 responseDateTimeRenderOption = "")
| ^~~~~~
c:\Users\hmski\Documents\Arduino\libraries\ESP-Google-Sheet-Client-master\src/ESP_Google_Sheet_Client.h:632:10: note: template argument deduction/substitution failed:
E:\FYP Deg\Updating\Updating.ino:136:45: note: cannot convert '& response' (type 'String') to type 'FirebaseJson'
136 | bool success = GSheet.values.update(&response /* returned response /, "" / spreadsheet Id to update /, "Sheet1!A1:B5" / range to update /, &valueRange / data to update /);
| ^~~~~~~~~
In file included from E:\FYP Deg\Updating\Updating.ino:17:
c:\Users\hmski\Documents\Arduino\libraries\ESP-Google-Sheet-Client-master\src/ESP_Google_Sheet_Client.h:696:10: note: candidate: 'template<class T1, class T2, class T3, class T4, class T5, class T6> bool GSheet_Values::update(String, T1, T2, FirebaseJson*, T3, T4, T5, T6)'
696 | bool update(String response, T1 spreadsheetId, T2 range, FirebaseJson valueRange, T3 valueInputOption = "USER_ENTERED", T4 includeValuesInResponse = "", T5 responseValueRenderOption = "", T6 responseDateTimeRenderOption = "")
| ^~~~~~
c:\Users\hmski\Documents\Arduino\libraries\ESP-Google-Sheet-Client-master\src/ESP_Google_Sheet_Client.h:696:10: note: template argument deduction/substitution failed:
E:\FYP Deg\Updating\Updating.ino:136:170: note: cannot convert '& valueRange' (type 'String') to type 'FirebaseJson'
136 | bool success = GSheet.values.update(&response /* returned response /, "" / spreadsheet Id to update /, "Sheet1!A1:B5" / range to update /, &valueRange / data to update */);
| ^~~~~~~~~~~
E:\FYP Deg\Updating\Updating.ino:137:18: error: 'class String' has no member named 'toString'; did you mean 'String'?
137 | response.toString(Serial, true);
| ^~~~~~~~
| String
Multiple libraries were found for "SD.h"
Used: C:\Users\hmski\AppData\Local\Arduino15\packages\esp8266\hardware\esp8266\3.1.1\libraries\SD
Not used: C:\Users\hmski\AppData\Local\Arduino15\libraries\SD
exit status 1

Compilation error: 'class String' has no member named 'add'

[mobizt]

You may don't get what I mean from previous reply.

Only the server response that you can use String object for the returning value from the function as this.

String response;

GSheet.values.update(&response, ...

The valueRange that passed to the function is for the payload to send to server not a response and it must be FirebaseJson object as in an example like this.

FirebaseJson valueRange;

You can't use String for valueRange.

I wrote it clearly in all examples that you can use String for response instead of FirebaseJson.

ESP-Google-Sheet-Client/examples/Values/Update/Update.ino

Lines 158 to 159 in 1de85f1

	// 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

Please read the update function description for detail.

[MeowCattoNiP]

Ohh i misunderstood you thinking that the payload can also be in string format. Okay I'll give it try. Thank you and sorry for the troubles! 😊🙈