Arduino Google Sheet Client Library for Arduino devices

Arduino Google Sheet Client Library for Arduino Devices.

This library works with Google Sheet APIs directly using the Service Account which is safe and reliable as it does not require Google AppScrips and other dirty HTTP hacks to work.

Devices will be able to read, update, append and clear sheet values. Creating, reading, listing, and deleting the spreadsheet is also supported.

The spreadsheet created using this library is owned by the Service Account and shared access to the user.

You can create, edit and deploy the Apps Script code via the extension of the spreadsheet created by this library except for running the script due to permission denied.

Spreadsheet created or owned by you needed to share the access with the Service Account's client email then, the library can read and edit except for deleting the user's spreadsheet due to permission denied.

Note: There is a way of hacking to post the data to Google Forms’s linked spreadsheet but it is inefficient and may not work when the form data model changes.

Dependencies

This library required the Arduino platforms SDK (for ESP32, ESP8266, Pico, SAMD stm32 and Teensy) to be installed.

To install device SDK, in Arduino IDE, the platform SDK can be installed through Boards Manager.

In PlatformIO IDE, the platform SDK can be installed through PIO Home > Platforms > Espressif 8266 or Espressif 32.

RP2040 Arduino SDK installation

For Arduino IDE, the Arduino-Pico SDK can be installed from Boards Manager by searching Pico and choosing Raspberry Pi Pico/RP2040 to install.

For PlatformIO, the Arduino-Pico SDK can be installed via platformio.ini

[env:rpipicow]
platform = https://github.com/maxgerhardt/platform-raspberrypi.git
board = rpipicow
framework = arduino
board_build.core = earlephilhower
monitor_speed = 115200
board_build.filesystem_size = 1m

See this Arduino-Pico SDK documentation for more information.

Prerequisites

This library uses the Service Account credentials to create the short-lived access token for Google API authentication, which will expire in 1 hour and will be refreshed automatically.

You need to create the Service Account private key and enable Google Sheet and Google Drive APIs for your project.

To enable Google Sheet API for your project, go to https://console.cloud.google.com/apis/library/sheets.googleapis.com

To enable Google Drive API for your project, go to https://console.cloud.google.com/apis/library/drive.googleapis.com

In case the spreadsheet that works with this library was created or owned by you, you need to share that spreadsheet with the Service Account client email.

If the spreadsheet is created by this library, it will set the share access to your account automatically, you will get the notification email of the file sharing.

How to Create Service Account Private Key

Go to Google Cloud Console.

Choose or create a project to create a Service Account.
Choose Service Accounts

Click at + CREAT SERVICE ACCOUNT.

Enter the Service account name,
Service account ID and
Click on CREATE AND CONTINUE

Select Role.
Click on CONTINUE.

Click on DONE.

Choose a service account that was recently created from the list.

Choose KEYS.

Click ADD KEY and choose Create new key.

Choose JSON for the Key type and click CREATE.

A private key will be created for this service account and downloaded to your computer, click CLOSE.

In the following steps (15-16) for saving the Service Account Credential in flash memory at compile time.

If you want to allow the library to read the Service Account JSON key file directly at run time, skip these steps.

Open the .json file that is already downloaded with the text editor.

{
  "type": "service_account",
  "project_id": "...",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "...",
  "client_id": "...",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "..."
}

Copy project_id, client_email, private_key_id and private_key from the .json file and paste them to these defines in the example.

#define PROJECT_ID "..." //Taken from "project_id" key in JSON file.
#define CLIENT_EMAIL "..." //Taken from "client_email" key in JSON file.
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n"; //Taken from "private_key" key in JSON file.

Installation

Using Library Manager

At Arduino IDE, go to menu Sketch -> Include Library -> Manage Libraries...

In the Library Manager Window, search "sheet" in the search form then select "ESP Google Spreadsheet Client".

Click "Install" button.

For PlatformIO IDE, use the following command.

pio lib install "ESP Google Sheet Client""

Or at PIO Home -> Library -> Registry then search ESP Google Sheet Client.

Manual installation

For Arduino IDE, download the zip file from the repository (Github page) by selecting Clone or download dropdown at the top of the repository, and select Download ZIP

From Arduino IDE, select menu Sketch -> Include Library -> Add .ZIP Library....

Choose ESP-Google-Sheet-Client-master.zip that was previously downloaded.

Go to the menu Files -> Examples -> ESP-Google-Sheet-Client-master and choose one from the examples.

Usages

See all examples for complete usages.

#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

#include <ESP_Google_Sheet_Client.h>

#define WIFI_SSID "WIFI_AP"
#define WIFI_PASSWORD "WIFI_PASSWORD"

#define PROJECT_ID "PROJECT_ID"

//Service Account's client email
#define CLIENT_EMAIL "CLIENT_EMAIL"

//Service Account's private key
const char PRIVATE_KEY[] PROGMEM = "-----BEGIN PRIVATE KEY-----XXXXXXXXXXXX-----END PRIVATE KEY-----\n";

bool taskComplete = false;

// For Pico, WiFiMulti is recommended.
#if defined(ARDUINO_RASPBERRY_PI_PICO_W)
WiFiMulti multi;
#endif

void setup()
{

    Serial.begin(115200);
    Serial.println();
    Serial.println();

    // Set auto reconnect WiFi or network connection
#if defined(ESP32) || defined(ESP8266)
    WiFi.setAutoReconnect(true);
#endif

// Connect to WiFi or network
#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();


    // 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);
    // You can add many WiFi credentials here
#endif

    //Begin the access token generation for Google API authentication
    GSheet.begin(CLIENT_EMAIL, PROJECT_ID, PRIVATE_KEY);

    // In case SD/SD_MMC storage file access, mount the SD/SD_MMC card.
    // SD_Card_Mounting(); // See src/GS_SDHelper.h

    // Or begin with the Service Account JSON file that uploaded to the Filesystem image or stored in SD memory card.
    // 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

        //If you assign the spreadsheet id from your own spreadsheet,
        //you need to set share access to the Service Account's CLIENT_EMAIL

        FirebaseJson response;
        // Instead of using FirebaseJson for response, you can use String for response to the functions 
        // especially in low memory devices that deserializing large JSON responses may be failed as in ESP8266

        Serial.println("Get spreadsheet values from range...");
        Serial.println("---------------------------------------------------------------");

        bool success = GSheet.values.get(&response /* returned response */, "<spreadsheetId>" /* spreadsheet Id to read */, "Sheet1!A1:A3" /* range to read */);
        response.toString(Serial, true);
        Serial.println();

        taskComplete = true;
    }
}

IDE Configuration for ESP8266 MMU - Adjust the Ratio of ICACHE to IRAM

Arduino IDE

When you update the ESP8266 Arduino Core SDK to v3.0.0, the memory can be configurable from Arduino IDE board settings.

By default MMU option 1 was selected, the free Heap can be low and may not be suitable for the SSL client usage in this library.

To increase the Heap, choose the MMU option 3, 16KB cache + 48KB IRAM and 2nd Heap (shared).

To use an external Heap from 1 Mbit SRAM 23LC1024, choose the MMU option 5, 128K External 23LC1024.

To use an external Heap from PSRAM, choose the MMU option 6, 1M External 64 MBit PSRAM.

The connection between SRAM/PSRAM and ESP8266

23LC1024/ESP-PSRAM64                ESP8266

CS (Pin 1)                          GPIO15
SCK (Pin 6)                         GPIO14
MOSI (Pin 5)                        GPIO13
MISO (Pin 2)                        GPIO12
/HOLD (Pin 7 on 23LC1024 only)      3V3
Vcc (Pin 8)                         3V3
Vcc (Pin 4)                         GND

PlatformIO IDE

By default, the balanced ratio (32KB cache + 32KB IRAM) configuration is used.

To increase the heap, PIO_FRAMEWORK_ARDUINO_MMU_CACHE16_IRAM48_SECHEAP_SHARED build flag should be assigned in platformio.ini.

[env:d1_mini]
platform = espressif8266
build_flags = -D PIO_FRAMEWORK_ARDUINO_MMU_CACHE16_IRAM48_SECHEAP_SHARED
board = d1_mini
framework = arduino
monitor_speed = 115200

And to use external Heap from 1 Mbit SRAM 23LC1024 and 64 Mbit PSRAM, PIO_FRAMEWORK_ARDUINO_MMU_EXTERNAL_128K and PIO_FRAMEWORK_ARDUINO_MMU_EXTERNAL_1024K build flags should be assigned respectively.

The supported MMU build flags in PlatformIO.

PIO_FRAMEWORK_ARDUINO_MMU_CACHE16_IRAM48

16KB cache + 48KB IRAM (IRAM)
PIO_FRAMEWORK_ARDUINO_MMU_CACHE16_IRAM48_SECHEAP_SHARED

16KB cache + 48KB IRAM and 2nd Heap (shared)
PIO_FRAMEWORK_ARDUINO_MMU_CACHE16_IRAM32_SECHEAP_NOTSHARED

16KB cache + 32KB IRAM + 16KB 2nd Heap (not shared)
PIO_FRAMEWORK_ARDUINO_MMU_EXTERNAL_128K

128K External 23LC1024
PIO_FRAMEWORK_ARDUINO_MMU_EXTERNAL_1024K

1M External 64 MBit PSRAM
PIO_FRAMEWORK_ARDUINO_MMU_CUSTOM

Disables default configuration and expects user-specified flags

To use PSRAM/SRAM for internal memory allocation you can config to use it via ESP_Google_Sheet_Client_FS_Config.h with this macro.

#define ESP_GOOGLE_SHEET_CLIENT_USE_PSRAM

Test code for MMU

#include <Arduino.h>
#include <umm_malloc/umm_heap_select.h>

void setup() 
{
  Serial.begin(115200);
  HeapSelectIram ephemeral;
  Serial.printf("IRAM free: %6d bytes\r\n", ESP.getFreeHeap());
  {
    HeapSelectDram ephemeral;
    Serial.printf("DRAM free: %6d bytes\r\n", ESP.getFreeHeap());
  }

  ESP.setExternalHeap();
  Serial.printf("External free: %d\n", ESP.getFreeHeap());
  ESP.resetHeap();
}

void loop() {
  // put your main code here, to run repeatedly:
}

Use PSRAM on ESP32

To enable PSRAM in ESP32 module with on-board PSRAM chip, in Arduino IDE

In PlatformIO in VSCode IDE, add the following build_flags in your project's platformio.ini file

build_flags = -DBOARD_HAS_PSRAM -mfix-esp32-psram-cache-issue

*When configuring the IDE or adding the build flags to use PSRAM in the ESP32 dev boards that do not have an on-board PSRAM chip, your device will crash (reset).

To use PSRAM for internal memory allocation you can config to use it via ESP_Google_Sheet_Client_FS_Config.h with this macro.

#define ESP_GOOGLE_SHEET_CLIENT_USE_PSRAM

Usage with TinyGSM

By default, the library assumes a working native WiFi or Ethernet connection. When using TinyGSM as an external client, you most likely do not want to use the Wifi/Ethernet (i.e. to get the current time). You will have to disable this in ESP_Google_Sheet_Client_FS_Config.h by uncommenting the following lines like this:

/* If not use onboard WiFi */
#define ESP_GOOGLE_SHEET_CLIENT_DISABLE_ONBOARD_WIFI

/* If not use native Ethernet (Ethernet interfaces that are supported by SDK) */
#define ESP_GOOGLE_SHEET_CLIENT_DISABLE_NATIVE_ETHERNET

Also, check out the GSM Example for more information.

Functions Description

Global Functions

Begin the Google API authentication.

param client_email (string) The Service Account's client email.

param project_id (string) The project ID.

param private_key (string) The Service Account's private key.

param eth (optional for ESP8266 only) The pointer to ESP8266 lwIP network class e.g. ENC28J60lwIP, Wiznet5100lwIP and Wiznet5500lwIP.

void begin(<string> client_email, <string> project_id, <string> private_key, <ESP8266_spi_eth_module> *eth = nullptr);

Begin the Google API authentication.

param service_account_file (string) The Service Account's JSON key file.

param storage_type (esp_google_sheet_file_storage_type) The JSON key file storage type e.g. esp_google_sheet_file_storage_type_flash and esp_google_sheet_file_storage_type_sd.

param eth (optional for ESP8266 only) The pointer to ESP8266 lwIP network class e.g. ENC28J60lwIP, Wiznet5100lwIP and Wiznet5500lwIP.

void begin(<string> service_account_file, esp_google_sheet_file_storage_type storage_type, <ESP8266_spi_eth_module> *eth = nullptr);

Set the OAuth2.0 token generation status callback.

param callback The callback function that accepts the TokenInfo as an argument.

void setTokenCallback(TokenStatusCallback callback);

Add the WiFi Access point credentials for connection resume (non-ESP device only).

param param ssid The access point ssid.

param param password The access point password.

void addAP(T1 ssid, T2 password);

Clear all WiFi Access points credentials (non-ESP device only).

void clearAP();

Assign external Arduino Client and required callback functions.

param client The pointer to Arduino Client derived class of SSL Client.

param networkConnectionCB The function that handles the network connection.

param networkStatusCB The function that handles the network connection status acknowledgement.

Due to the client pointer is assigned, to avoid dangling pointer, a client should exist as long as it was used for transportation.

void setExternalClient(Client *client, GS_NetworkConnectionRequestCallback networkConnectionCB,
                           GS_NetworkStatusRequestCallback networkStatusCB);

Assign TinyGsm Clients.

param client The pointer to TinyGsmClient.

param modem The pointer to TinyGsm modem object. The modem should be initialized and/or set mode before transferring data.

param pin The SIM pin.

param apn The GPRS APN (Access Point Name).

param user The GPRS user.

param password The GPRS password.

Due to the client and modem pointers being assigned, to avoid dangling pointer, a client should exist as long as it is used for transportation.

void setGSMClient(Client *client, void *modem, const char *pin, const char *apn, const char *user, const char *password);

Set the network status acknowledgement.

param status The network status.

void setNetworkStatus(bool status);

Set the seconds to refresh auth token before it expires.

param seconds The seconds (60 sec to 3540 sec) that auth token will refresh before expired.

The default value is 300 seconds.

void setPrerefreshSeconds(uint16_t seconds);

Set the Root certificate data for server authorization

param ca PEM format certificate string.

void setCert(const char *ca);

Set the Root certificate file for server authorization.

param filename PEM format certificate file name included path.

param storageType The storage type of the certificate file. esp_google_sheet_file_storage_type_flash or esp_google_sheet_file_storage_type_sd

void setCertFile(<string> filename, esp_google_sheet_file_storage_type storageType);

Get the authentication ready status and process the authentication.

Note: This function should be called repeatedly in loop.

bool ready();

Get the generated access token.

retuen String of OAuth2.0 access token.

String accessToken();

Get the token type string.

param info The TokenInfo structured data contains token info.

retuen String of token type.

String getTokenType();

String getTokenType(TokenInfo info);

Get the token status string.

param info The TokenInfo structured data contains token info.

retuen String of token status.

String getTokenStatus();

String getTokenStatus(TokenInfo info);

Get the token generation error string.

param info The TokenInfo structured data contains token info.

retuen String of token error.

String getTokenError();

String getTokenError(TokenInfo info);

Get the token expiration timestamp (seconds from midnight Jan 1, 1970).

retuen unsigned long of timestamp.

unsigned long getExpiredTimestamp();

Get the current timestamp.

return timestamp

uint64_t getCurrentTimestamp();

Set system time with a timestamp.

param ts timestamp in seconds from midnight Jan 1, 1970.

return Boolean type status indicates the success of the operation.

bool setSystemTime(time_t ts);

Force the token to expire immediately and refresh.

void refreshToken();

Reset stored config and auth credentials.

void reset();

Initiate SD card with SPI port configuration.

param ss The SPI Chip/Slave Select pin.

param sck The SPI Clock pin.

param miso The SPI MISO pin.

param mosi The SPI MOSI pin.

aram frequency The SPI frequency.

return boolean The boolean value indicates the success of the operation.

bool sdBegin(int8_t ss = -1, int8_t sck = -1, int8_t miso = -1, int8_t mosi = -1, uint32_t frequency = 4000000);

Initiate SD card with SD FS configurations (ESP8266 only).

param ss SPI Chip/Slave Select pin.

param sdFSConfig The pointer to SDFSConfig object (ESP8266 only).

return boolean type status indicates the success of the operation.

  bool sdBegin(SDFSConfig *sdFSConfig);

Initiate SD card with chip select and SPI configuration (ESP32 only).

param ss The SPI Chip/Slave Select pin.

param spiConfig The pointer to SPIClass object for SPI configuration.

param frequency The SPI frequency.

return boolean The boolean value indicates the success of the operation.

bool sdBegin(int8_t ss, SPIClass *spiConfig = nullptr, uint32_t frequency = 4000000);

Initiate SD card with SdFat SPI and pins configurations (with SdFat included only).

param sdFatSPIConfig The pointer to SdSpiConfig object for SdFat SPI configuration.

param ss The SPI Chip/Slave Select pin.

param sck The SPI Clock pin.

param miso The SPI MISO pin.

param mosi The SPI MOSI pin.

return boolean The boolean value indicates the success of the operation.

 bool sdBegin(SdSpiConfig *sdFatSPIConfig, int8_t ss = -1, int8_t sck = -1, int8_t miso = -1, int8_t mosi = -1);

Initiate SD card with SdFat SDIO configuration (with SdFat included only).

param sdFatSDIOConfig The pointer to SdioConfig object for SdFat SDIO configuration.

return boolean The boolean value indicates the success of the operation.

 bool sdBegin(SdioConfig *sdFatSDIOConfig);

Initialize the SD_MMC card (ESP32 only).

param mountpoint The mounting point.

param mode1bit Allow 1-bit data line (SPI mode).

param format_if_mount_failed Format SD_MMC card if mount failed.

return Boolean type status indicates the success of the operation.

bool sdMMCBegin(const char *mountpoint = "/sdcard", bool mode1bit = false, bool format_if_mount_failed = false);

Formatted printing on Serial.

void printf(const char *format, ...);

Get free Heap memory.

return Free memory amount in byte

int getFreeHeap();

Applies one or more updates to the spreadsheet.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (string) The spreadsheet to apply the updates to.

param requestsArray (FirebaseJsonArray of Request object) A list of updates to apply to the spreadsheet.

Note: Requests will be applied in the order they are specified. If any request is not valid, no requests will be applied.

param includeSpreadsheetInResponse (boolean string) Determines if the update response should include the spreadsheet resource.

param responseRanges (string) Limits the ranges included in the response spreadsheet. Meaningful only if includeSpreadsheetInResponse is 'true'.

param responseIncludeGridData (boolean string) True if grid data should be returned. Meaningful only if includeSpreadsheetInResponse is 'true'. This parameter is ignored if a field mask is set in the request.

return Boolean type status indicates the success of the operation.

For ref doc go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

bool batchUpdate(FirebaseJson *response, <string> spreadsheetId, FirebaseJsonArray *requestsArray, <string> includeSpreadsheetInResponse = "", <string> responseRanges = "", <string> responseIncludeGridData = "");

bool batchUpdate(String *response, <string> spreadsheetId, FirebaseJsonArray *requestsArray, <string> includeSpreadsheetInResponse = "", <string> responseRanges = "", <string> responseIncludeGridData = "");

Creates a spreadsheet, returning the newly created spreadsheet.

param response (FirebaseJson or String) The returned response.

param spreadsheet (FirebaseJson) The spreadsheet object.

param sharedUserEmail (string) Email of the user to share the access.

Note Google Drive API should be enabled at, https://console.cloud.google.com/apis/library/drive.googleapis.com

return Boolean type status indicates the success of the operation.

For ref doc go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/create

bool create(FirebaseJson *response, FirebaseJson *spreadsheet, <string> sharedUserEmail);

bool create(String *response, FirebaseJson *spreadsheet, <string> sharedUserEmail);

Get the spreadsheet at the given ID.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (FirebaseJson) The spreadsheet to request.

param ranges (string) The ranges to retrieve from the spreadsheet. Ranges separated with comma ",".

param includeGridData (boolean string) True if grid data should be returned.

Note This parameter is ignored if a field mask was set in the request.

return Boolean type status indicates the success of the operation.

For ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get

bool get(FirebaseJson *response, <string> spreadsheetId, <string> ranges = "", <string> includeGridData = "");

bool get(String *response, <string> spreadsheetId, <string> ranges = "", <string> includeGridData = "");

Get the spreadsheet at the given ID.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (FirebaseJson) The spreadsheet to request.

return Boolean type status indicates the success of the operation.

For ref doc, go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/getByDataFilter

bool getByDataFilter(FirebaseJson *response, <string> spreadsheetId, FirebaseJsonArray *dataFiltersArray, <string> includeGridData = "");

bool getByDataFilter(String *response, <string> spreadsheetId, FirebaseJsonArray *dataFiltersArray, <string> includeGridData = "");

Delete a spreadsheet from Google Drive.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (FirebaseJson) The ID of the spreadsheet to delete.

param closeSession (boolean) Close the session after delete.

return Boolean type status indicates the success of the operation.

Note The response may be empty.

bool deleteFile(FirebaseJson *response, <string> spreadsheetId, bool closeSession = true);

bool deleteFile(String *response, <string> spreadsheetId, bool closeSession = true);

Delete spreadsheets from Google Drive.

param response (FirebaseJson or String) The returned response.

Note This will delete the last 5 spreadsheets at a time.

return Boolean type status indicates the success of the operation.

Note: The response may be empty.

bool deleteFiles(FirebaseJson *response);

bool deleteFiles(String *response);

List the spreadsheets in Google Drive.

param response (FirebaseJson or String) The returned response.

param pageSize (integer) The maximum number of files to return per page.

param orderBy (string) A comma-separated list of sort keys.

Note: Valid keys are createdTime, folder, modifiedByMeTime, modifiedTime, name, name_natural, quotaBytesUsed, recency, sharedWithMeTime, starred, and viewedByMeTime.

Each key sorts ascending by default, but may be reversed with the desc modifier.

Example usage: folder,modifiedTime%20desc,name which the white space needs to be replaced with %20 as this parameter is used as the URI parameter of the Google Drive API request endpoint.

Please consult Google Drive API doc for more details.

param pageToken (string) The token for continuing a previous list request on the next page.

Note: This should be set to the value of 'nextPageToken' from the previous response.

return Boolean type status indicates the success of the operation.

bool listFiles(FirebaseJson *response, uint32_t pageSize = 5, <string> orderBy = "createdTime%20desc", <string> pageToken = "");

bool listFiles(String *response, uint32_t pageSize = 5, <string> orderBy = "createdTime%20desc", <string> pageToken = "");

Spreadsheets.Values member functions

Get a range of values from a spreadsheet.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (string) The ID of the spreadsheet to retrieve data from.

param range (string) The A1 notation or R1C1 notation of the range to retrieve values from.

return Boolean type status indicates the success of the operation.

For ref doc go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

bool get(FirebaseJson *response, <string> spreadsheetId, <string> range);

bool get(String *response, <string> spreadsheetId, <string> range);

Get one or more ranges of values from a spreadsheet.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (string) The ID of the spreadsheet to retrieve data from.

param ranges (string) The A1 notation or R1C1 notation of the range to retrieve values from. Ranges separated with comma ",".

param majorDimension (enum string) The major dimension that results should use.

Note If the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS returns [[1,2],[3,4]], whereas requesting range=A1:B2,majorDimension=COLUMNS returns [[1,3],[2,4]].

DIMENSION_UNSPECIFIED The default value, do not use.

ROWS Operates on the rows of a sheet.

COLUMNS Operates on the columns of a sheet.

param valueRenderOption (enum string) How values should be represented in the output.

Note: The default render option is ValueRenderOption.FORMATTED_VALUE.

FORMATTED_VALUE Values will be calculated & formatted in the reply according to the cell's formatting.

Formatting is based on the spreadsheet's locale, not the requesting user's locale.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "$1.23".

UNFORMATTED_VALUE Values will be calculated, but not formatted in the reply.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

FORMULA Values will not be calculated. The reply will include the formulas.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "=A1".

param dateTimeRenderOption (enum string) How dates, times, and durations should be represented in the output.

Note: This is ignored if valueRenderOption is FORMATTED_VALUE.

The default dateTime render option is SERIAL_NUMBER. SERIAL_NUMBER Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3.

The whole number portion of the value (left of the decimal) counts the days since December 30th, 1899.

The fractional portion (right of the decimal) counts the time as a fraction of the day.

For example, January 1st, 1900 at noon would be 2.5, 2 because it's 2 days after December 30th 1899, and .5 because noon is half a day.

February 1st, 1900 at 3 pm would be 33.625. This correctly treats the year 1900 as not a leap year.

FORMATTED_STRING Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which is dependent on the spreadsheet locale).

return Boolean type status indicates the success of the operation.

For ref doc go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet

bool batchGet(FirebaseJson *response, <string> spreadsheetId, <string> ranges, <string> majorDimension = "", <string> valueRenderOption = "", <string> dateTimeRenderOption = "");

bool batchGet(String *response, <string> spreadsheetId, <string> ranges, <string> majorDimension = "", <string> valueRenderOption = "", <string> dateTimeRenderOption = "");

Get one or more ranges of values that match the specified data filters.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (string) The ID of the spreadsheet to retrieve data from.

param dataFiltersArray (FirebaseJsonArray of DataFilter object) The data filters are used to match the ranges of values to retrieve. Ranges that match any of the specified data filters are included in the response.

param majorDimension (enum string) The major dimension that results should use.

Note: If the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then requesting range=A1:B2,majorDimension=ROWS returns [[1,2],[3,4] whereas requesting range=A1:B2,majorDimension=COLUMNS returns [[1,3],[2,4]].

DIMENSION_UNSPECIFIED The default value, do not use.

ROWS Operates on the rows of a sheet.

COLUMNS Operates on the columns of a sheet.

param valueRenderOption (enum string) How values should be represented in the output.

Note: The default render option is ValueRenderOption.FORMATTED_VALUE.

FORMATTED_VALUE Values will be calculated & formatted in the reply according to the cell's formatting.

Formatting is based on the spreadsheet's locale, not the requesting user's locale.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "$1.23". UNFORMATTED_VALUE Values will be calculated, but not formatted in the reply.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23. FORMULA Values will not be calculated. The reply will include the formulas.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "=A1".

param dateTimeRenderOption (enum string) How dates, times, and durations should be represented in the output.

Note: This is ignored if valueRenderOption is FORMATTED_VALUE.

The default dateTime render option is SERIAL_NUMBER.

SERIAL_NUMBER Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3.

The whole number portion of the value (left of the decimal) counts the days since December 30th, 1899.

The fractional portion (right of the decimal) counts the time as a fraction of the day.

For example, January 1st, 1900 at noon would be 2.5, 2 because it's 2 days after December 30th 1899, and .5 because noon is half a day.

February 1st, 1900 at 3 pm would be 33.625. This correctly treats the year 1900 as not a leap year.

FORMATTED_STRING Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which is dependent on the spreadsheet locale).

return Boolean type status indicates the success of the operation.

For the ref doc go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGetByDataFilter

bool batchGetByDataFilter(FirebaseJson *response, <string> spreadsheetId, FirebaseJsonArray *dataFiltersArray, <string> majorDimension, <string> valueRenderOption = "", <string> dateTimeRenderOption = "");

bool batchGetByDataFilter(String *response, <string> spreadsheetId, FirebaseJsonArray *dataFiltersArray, <string> majorDimension, <string> valueRenderOption = "", <string> dateTimeRenderOption = "");

Appends values to a spreadsheet.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (string) The ID of the spreadsheet to update.

param range (string) The A1 notation of a range to search for a logical table of data.

Values are appended after the last row of the table.

param valueRange (FirebaseJson of valueRange object) The data within a range of the spreadsheet.

param valueInputOption (enum string) How the input data should be interpreted.

Note:

INPUT_VALUE_OPTION_UNSPECIFIED Default input value. This value must not be used.

RAW The values the user has entered will not be parsed and will be stored as-is.

USER_ENTERED The values will be parsed as if the user typed them into the UI.

Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.

param insertDataOption (enum string) How the input data should be inserted.

Note:

OVERWRITE The new data overwrites existing data in the areas it is written. (Note: adding data to the end of the sheet will still insert new rows or columns so the data can be written.) or

INSERT_ROWS Rows are inserted for the new data.

param includeValuesInResponse (boolean string). Determines if the update response should include the values of the cells that were appended.

Note: By default, responses do not include the updated values.

param responseValueRenderOption (enum string) Determines how values in the response should be rendered.

Note:

The default render option is FORMATTED_VALUE.

FORMATTED_VALUE Values will be calculated & formatted in the reply according to the cell's formatting.

Formatting is based on the spreadsheet's locale, not the requesting user's locale.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "$1.23".

UNFORMATTED_VALUE Values will be calculated, but not formatted in the reply.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

FORMULA Values will not be calculated. The reply will include the formulas.

For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return "=A1".

param responseDateTimeRenderOption (enum string) Determines how dates, times, and durations in the response should be rendered.

Note: This is ignored if responseValueRenderOption is FORMATTED_VALUE.

The default dateTime render option is SERIAL_NUMBER.

SERIAL_NUMBER Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3.

The whole number portion of the value (left of the decimal) counts the days since December 30th, 1899.

The fractional portion (right of the decimal) counts the time as a fraction of the day.

For example, January 1st, 1900 at noon would be 2.5, 2 because it's 2 days after December 30th, 1899, and .5 because noon is half a day.

February 1st, 1900 at 3 pm would be 33.625. This correctly treats the year 1900 as not a leap year.

FORMATTED_STRING Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which is dependent on the spreadsheet locale).

return Boolean type status indicates the success of the operation.

For ref doc go to https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append

bool append(FirebaseJson *response, <string> spreadsheetId, <string> range, FirebaseJson *valueRange, <string> valueInputOption = "USER_ENTERED", <string> insertDataOption = "", <string> includeValuesInResponse = "", <string> responseValueRenderOption = "", <string> responseDateTimeRenderOption = "");

bool append(String *response, <string> spreadsheetId, <string> range, FirebaseJson *valueRange, <string> valueInputOption = "USER_ENTERED", <string> insertDataOption = "", <string> includeValuesInResponse = "", <string> responseValueRenderOption = "", <string> responseDateTimeRenderOption = "");

Sets values in a range of a spreadsheet.

param response (FirebaseJson or String) The returned response.

param spreadsheetId (string) The ID of the spreadsheet to update.

param range (string) The A1 notation of the values to update.