Google sheet integration

Good night everyone. I'm having problems with integration with Google Sheet. The following error is shown when I click test in the Particle integrations panel:

"The test of your integration was unsuccessful.
The attempt failed with the following error message:
error status 401 from script.google.com
Double check that you configured your integration correctly. You may need to re-create it if this failure persists."

I followed the tutorial available at "Publish to Google Sheets | Integrations | Particle". Thank you in advance for your help.

401 is permission denied by Google. The most common reason is that the permission to run the script is not set to Anyone. That is required.

Good morning and thank you for your attention. I ran the script and everything is authorized. Receiving the message: notification started and notification completed. So I believe the permission part is ok.

You ran the script from the apps script editor? That won't test the authentication because if it's set to the "my account" it will succeed because the script editor is logged into your Google account.

When the Particle integration runs it will not be logged into any Google account because it's running on the cloud server, not on your computer.

In any case, it's probably not an error on the Particle side because the Google apps script server is what is returning 401 not authorized.

1 Like

Thanks for the tip. In fact, the failure was not a script that concerns access. After selecting the " Anyone" option, the error disappeared and the spreadsheet updated according to the particle tutorial. Now I realized that in the Google spreadsheet an integer value is displayed every 30 seconds and in the particle events tool the values ​​are decimal. Another observation is that to send data to the Google Sheet spreadsheet it is necessary to use the "Particle.publish(eventName, buf, PRIVATE)" function. Because I commented on this code function and Google Sheet no longer received the information.

tutorial particle google sheet:

#include "Particle.h"

SYSTEM_THREAD(ENABLED);

SerialLogHandler logHandler;

// How often to publish a value
const std::chrono::milliseconds publishPeriod = 30s;

// The event name to publish with
const char *eventName = "sheetTest1";

unsigned long lastPublish;
int counter = 0;

void publishTest();

void setup() {
}

void loop() {
    if (Particle.connected()) {
        if (millis() - lastPublish >= publishPeriod.count()) {
            lastPublish = millis();

            publishTest();
        }
    }
}

void publishTest() {
    char buf[128];

    snprintf(buf, sizeof(buf), "[%d,%d]", ++counter, rand());

    Particle.publish(eventName, buf, PRIVATE);
    Log.info("published: %s", buf);
}