# Webhook builder to google sheets

**URL:** <https://community.particle.io/t/webhook-builder-to-google-sheets/43012>\
**Category:** Firmware\
**Created:** [July 14, 2018, 10:21pm UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012 "2018-07-14T22:21:04Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![jjlee32](https://avatars.discourse-cdn.com/v4/letter/j/df705f/32.png) [@jjlee32](https://community.particle.io/u/jjlee32)\
**Post date:** [July 14, 2018, 10:21pm UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/1 "2018-07-14T22:21:04Z")

</div>

Friends,

I am attempting to post data to a google sheet from a particle device. I currently have a ambient temp & humidity sensor and a water temp sensor and want to have that data post to a google doc every 2 hours. I only have three values right now but I am wanting to make it easy to add more values if at all possible.

This is my first try interfacing with webhooks so please be brutally honest with what i am doing wrong. I have found a number of helpful tutorials that have helped me build the webhook but when the `Particle.publish(data,dataStr)` publishes i get an error in the particle logs(screenshot below).

 ![48%20PM](https://us1.discourse-cdn.com/flex026/uploads/particle/original/3X/7/9/7915bbde324888fff42c81c8d71e9dbb4d4c7927.png)

The tutortial i found the most helpful, and followed the most closley is this one [https://www.hackster.io/gusgonnet/pushing-data-to-google-docs-02f9c4](https://www.hackster.io/gusgonnet/pushing-data-to-google-docs-02f9c4)

It was very helpful but i was (still am) confused on the formatting of the data in my particle web ide code, and how that all interacts with the JSON and the google script code.

I don’t want to use any third party services, as i want to keep it as simple as possible. Here is a screen shot of the webhook and where i think I am wrong.

 ![02%20PM](https://us1.discourse-cdn.com/flex026/uploads/particle/original/3X/5/4/54750aab7c226f295e731539eef51a72ccc0a25b.png)

I currently have no code in the google sheet, i only have it deployed as a web app,

here is the particle code i am using.

```auto
// This #include statement was automatically added by the Particle IDE.
#include "DS18.h"
// This #include statement was automatically added by the Particle IDE.
#include <Adafruit_DHT.h>
// This #include statement was automatically added by the Particle IDE.
#include <elapsedMillis.h>

#define ambientPin D4
#define DHTTYPE DHT11
#define SprayPin D2
#define WaterTempPin D3

/ ****************************INPUTS***************************************** /
int32_t OnTime = 10000; //Time in milliseconds, how long you want to spray for
int32_t OffTime = 60000; //Time in milliseconds, how long to be off in between cycles

/ *************************************************************************** /

elapsedMillis timer0; // a running timer to keep track of ON time
bool Spraying = false; // a flag used to tell us if the pump is on
int32_t mainOnTime = -1;
int32_t mainOffTime = -1;
int32_t resetTime = 40*24*3600*1000;

double ambTempF = -1; //Setting up ambient sensor
double ambHumid = -1;
DHT dht(ambientPin, DHTTYPE);

double waterTempF=-1; //Setting up water temp sensor
double waterTempC=-1;
DS18 sensor(WaterTempPin);

String resultstr=String(64); // String to be used in the particle publish

void setup() {
    
    Serial.begin(9600);
    pinMode(SprayPin, OUTPUT); // Telling the controller this pin will be used as a Digital output
    pinMode(ambientPin, INPUT);
    digitalWrite(SprayPin, LOW); // On start up keep output off
    pinMode(WaterTempPin,INPUT);
    
   
    Particle.variable("waterTempF",waterTempF);
    Particle.variable("AmbientTemp", ambTempF);
    Particle.variable("Humidity", ambHumid);
    
    
}

void loop() {
    
    ambTempF = dht.getTempFarenheit(); //fetching ambient temp and humidity from DH11
    ambHumid = dht.getHumidity();
    
    
    //WATER TEMP SENSOR
    if(sensor.read()){
        waterTempF=sensor.fahrenheit();
        waterTempC=sensor.celsius();
        //Serial.println();
        //Serial.printf("Temperature %.2f C %.2f F ", waterTempC,waterTempF );
       // Serial.println(waterTempC);
        //Serial.println(waterTempF);
    }
    
    
    if(timer0%5000==0){
        //Serial.println("Temp: ");
        //Serial.println(ambTempF);
        //Serial.println("Humidity: ");
        //Serial.println(ambHumid);
        //resultstr = String(ambTempF) +" "+ String(ambHumid);
        //Serial.println(resultstr);
        Particle.publish("googleDocs", "{\"my-name\":\"" + resultstr + "\"}", 60, PRIVATE);
        //Particle.publish("googleDocs", resultstr, 60, PRIVATE);
    } //used for doing things at a certain time interval
    
    
    
    if(!Spraying){ // turns the spray pin on
        if(timer0-mainOffTime>OffTime){
            Spraying=true;
            spayersOn();
        }
    }
    if(Spraying){ // turns the sprayers off
        if(timer0-mainOnTime>OnTime){
            Spraying=false;
            spayersOff();
        
        }
    }
   
   
    
}

void spayersOn(){
    digitalWrite(SprayPin,HIGH);
    mainOnTime=timer0;
}
void spayersOff(){
    digitalWrite(SprayPin,LOW);
    mainOffTime=timer0;
}

```

any insight or good resources on how to pass data from server to server is greatly appreciated.

---

<div class="post-metadata">

**Author:** ![ScruffR](https://sea2.discourse-cdn.com/flex026/user_avatar/community.particle.io/scruffr/32/6952_2.png) [@ScruffR](https://community.particle.io/u/ScruffR)\
**Post date:** [July 15, 2018, 7:08am UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/2 "2018-07-15T07:08:47Z")

</div>

I’d stay away from `String` and use [`snprintf()`](http://www.cplusplus.com/reference/cstdio/snprintf/) and a `char buf[]` instead.  
Rationale behind that is provided all over this forum and has to do with heap fragmentation.

```cpp
  char buf[64];
  snprintf(buf, sizeof[buf], "{\"temp\":\"%.2f\", \"hum\":\"%.2f\"}", ambTempF, ambHumid); 
  Particle.publish("googleDocs", buf, PRIVATE);

```

HTTP error 401 would suggest your authorisation failed.

---

<div class="post-metadata">

**Author:** ![jjlee32](https://avatars.discourse-cdn.com/v4/letter/j/df705f/32.png) [@jjlee32](https://community.particle.io/u/jjlee32)\
**Post date:** [July 15, 2018, 11:05pm UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/3 "2018-07-15T23:05:40Z")

</div>

Thanks for the insight, is there an upper limit to size of the string i can construct in the buf variable? or is just limited by the size of the string you can pass through particle publish?

> [@ScruffR](#):
>
> HTTP error 401 would suggest your authorisation failed.

I don't recall setting up the autorization in the webhook builder. I think i am going to try using the custom template, but still do not know where to put the authization information.

Here's what i have so far:

```auto
{
    "event": "googleDocs",
    "url": "https://script.google.com/a/omitted",
    "requestType": "POST",
    "noDefaults": false,
    "rejectUnauthorized": true
}

```

my questions is where do I put the authorization, and the other information about the data i am sending?

Since making this post I tried using IFTTT and got the device to send data but the string i published didn't show up in the columns . The time stamp and device name did no problem. It was hard to test what i was doing wrong as the applets don't run on regular intervals.

I also noticed the google platform integration on the particle console, i haven't checked it out yet, but would that be something that may be easier to interface with?

Thanks again

---

<div class="post-metadata">

**Author:** ![jjlee32](https://avatars.discourse-cdn.com/v4/letter/j/df705f/32.png) [@jjlee32](https://community.particle.io/u/jjlee32)\
**Post date:** [July 16, 2018, 4:28am UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/4 "2018-07-16T04:28:03Z")

</div>

update, well sorta:

I have looked into webhooks all day and feel a little more comfortable and a little more confused at the same time.

I was able to get the webhook integration to successfully run (according to the particle console). I think that it had to do with the web app being deployed to allow anyone to post to it, and that wasn’t possible with a school gmail account, so i used a personal one. However I modified my code and integration to be more like the original link i posted above. I did this to try and follow the tutorial as close as possible.

So it seems I am getting to data to the google script servers but there is nothing showing on the sheet. I have found google script code that seems like it’s built to do such a thing. [this one for example](https://community.particle.io/t/pushing-data-from-the-photon-into-google-sheets/26108/29?u=gusgonnet). should i be looking into this further? With iftt the all of the parsing of the data, setting up the sheet, etc was done for me. Is the google script code needed to properly display the data passed by the webhook integration?

---

<div class="post-metadata">

**Author:** ![jjlee32](https://avatars.discourse-cdn.com/v4/letter/j/df705f/32.png) [@jjlee32](https://community.particle.io/u/jjlee32)\
**Post date:** [July 22, 2018, 6:46am UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/5 "2018-07-22T06:46:14Z")

</div>

Update. I have gotten the sheet to talk to the photon. The webhook is working has posted data using “Web From” way of processing the data but i had trouble getting more than one value to post on the sheet. So I am trying to use the Custom Template feature in the Webhook buidler and passing JSON data.

Here is the publish function I am using in my code

```auto
        char data[64];
        snprintf(data, sizeof(data), "{\"ambTempF\":%f,\"ambHumid\":%f}", ambTempF, ambHumid);
        Particle.publish("googleDocs", data, 60,PRIVATE);

```

and my webhook definition I used the format from this [post.](https://community.particle.io/t/webhook-with-more-then-one-value/23730/7?u=jjlee32)

```auto
{
    "event": "googleDocs",
    "url": "https://script.google.com/macros/blahblahblah",
    "requestType": "POST",
    "noDefaults": true,
    "rejectUnauthorized": false,
    "json": {
        "ambTempF": "{{{ambTempF}}}",
        "ambHumid": "{{{ambHumid}}}"
    }
}

```

I am using the google API script found in this [post.](https://community.particle.io/t/pushing-data-from-the-photon-into-google-sheets/26108/29?u=jjlee32)

I have it to publish every 60 seconds and I know if works because of the Timestamp header function in the google script. Every time it hits the timestamp is posted but the other two rows have undefined. I have a hunch that it is how I am publishing the data or how the sheet is reading the data. Here is what the event log returns;  
`{"data":"{\"ambTempF\":32.000000,\"ambHumid\":0.000000}","ttl":60,"published_at":"2018-07-22T04:37:22.195Z","coreid":"Omitted","name":"googleDocs"}`

I am so close i can taste it. I do appreciate having to figure it out for myself, helps me understand the inner workings better. Any insight is greatly appreciated.

---

<div class="post-metadata">

**Author:** ![jjlee32](https://avatars.discourse-cdn.com/v4/letter/j/df705f/32.png) [@jjlee32](https://community.particle.io/u/jjlee32)\
**Post date:** [August 10, 2018, 1:35am UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/6 "2018-08-10T01:35:49Z")

</div>

Friends,

Great news, I figured it out. Thanks to the weather of great tutorials and posts I was able to hack together something that works. I wanted to return and share the secret formula that worked for me, because i know i am not the only person struggling with this.

To be clear, the goal of this post was to figure out a way to get multiple data values from the photon to their own respective columns.

In my case I had three variable; ambTempF, ambHumid, and waterTempF. In my photon code the sensor data is updated and published every hour. using a ellapsedMillis timer, like so;

```auto
 if(timer0%publishInterval==0){ //every hour these will push to every hour to google sheets

    Particle.publish("googleDocs", "{\"ambTempF\":\"" + String(ambTempF) + "\",\"ambHumid\":\"" + String(ambHumid) +"\",\"waterTempF\":\"" + String(waterTempF) +"\"}", 60, PRIVATE);
}

```

I add each data entry using the above format, not sure if its the best way to cast the data to the string, but it works.

Once i confirmed the data was being published on the particle data feed, i went to construct the webhook.

using the webhook builder:

- event name:“googleDocs” - must match the event in the `Particle.publish` command
- url: go to google sheet script editor, deploy as web app, copy and paste that url
- Request type: POST
- device: ANY - or your target device
- under advanced settings enter your variable names like so;  
 ![42%20PM](https://us1.discourse-cdn.com/flex026/uploads/particle/original/3X/4/9/49febe69fd3ac58be69bcda1164d6d144236e1bd.png)  
then in the headers section ( not sure if this is necessary)  
 ![53%20PM](https://us1.discourse-cdn.com/flex026/uploads/particle/original/3X/0/c/0cc0ffdc91562dfd9419a04771fa1f12925c66c6.png)

now you can test the webhook and see if its hitting the url and getting a response by using the test button in the webhook builder. The last step is to configure the google sheet.

first go to the sheet you want to use, and name the cell A1 " Timestamp" and then name the others to match what we put in the webhook builder (i kept them all the same as the variable name). Here is what my sheet looks like  
 ![35%20PM](https://us1.discourse-cdn.com/flex026/uploads/particle/original/3X/8/2/82eae10fdfb38422a11f31e732b462b3729cb87a.png)  
Here is the code i used on the sheet, there are some instructions in the code as to how to set it up. I do not own the code, nor do i remember where it came from but someone deserves credit.

```auto
// 1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";

// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}

function doPost(e){
   return handleResponse(e);
}

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000); // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];

    var json_obj = JSON.parse(e.postData.contents); 
    
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(json_obj[headers[i]]);
      }
    }

    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

```

I hope this helps someone. Feel free to enlighten me with ways to improve it. Thanks for the help.

---

<div class="post-metadata">

**Author:** ![DRCO](https://sea2.discourse-cdn.com/flex026/user_avatar/community.particle.io/drco/32/5268_2.png) [@DRCO](https://community.particle.io/u/DRCO)\
**Post date:** [December 9, 2019, 4:40am UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/7 "2019-12-09T04:40:09Z")

</div>

Hi Particles,  
I came across this thread and have tried unsuccessfully to implement it  
My event is called sendToDemo but otherwise it’s pretty much as posted here…

- my hook response is:  
{  
“result”: “error”,  
“error”: {  
“message”: “Unexpected token: v”,  
“name”: “SyntaxError”,  
“fileName”: “Code (DEMO\_SCRIPT)”,  
“lineNumber”: 44,  
“stack”: “\tat Code (DEMO\_SCRIPT):44 (handleResponse)\n\tat Code (DEMO\_SCRIPT):22 (doPost)\n”  
}  
}  
any ideas?

---

<div class="post-metadata">

**Author:** ![armor](https://sea2.discourse-cdn.com/flex026/user_avatar/community.particle.io/armor/32/18398_2.png) [@armor](https://community.particle.io/u/armor)\
**Post date:** [December 9, 2019, 1:34pm UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/8 "2019-12-09T13:34:17Z")

</div>

Can you post the script that you are using and a screenshot of the webhook setup - impossible otherwise for others to help you.

---

<div class="post-metadata">

**Author:** ![DRCO](https://sea2.discourse-cdn.com/flex026/user_avatar/community.particle.io/drco/32/5268_2.png) [@DRCO](https://community.particle.io/u/DRCO)\
**Post date:** [December 9, 2019, 10:23pm UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/9 "2019-12-09T22:23:54Z")

</div>

Thanks @armor for coming back to me. I thought someone might have recognised the error code but you’re right, not providing the code isn’t helpful. I’ve got this sorted - and it’s the format of the Webhook that is the problem. To fix it I’m using a custom JSON script and getting data through to the right spreadsheet columns  
Particle code:  
Pretty straightforward, click a button connected to D3 - publish some random data from a couple of pots on A2 and A3. Please note: I adopted @ScruffR 's suggestion of using snprintf() instead of String.  
Code is as follows:

```cpp
char buf[64];
  
  int temPin = A2;
  int volPin = A3;
  int goNow = D3;
  double tempRead = 0;
  double volRead= 0;
  void setup(){
    
   
   pinMode(temPin, INPUT); 
   pinMode(temPin, INPUT);
   pinMode(goNow, INPUT);
    
}
void loop(){
    int sendNow = digitalRead(goNow);
    if(sendNow == HIGH){
    
		    int	randNumber = random(100, 300);  
            int randNumber2 = random(120, 330);
            int temPread_raw =analogRead(temPin);
            int vOLread_raw = analogRead(volPin);
  
			int dataA = (temPread_raw *3.4/4094.00*1000);
            int dataB = (vOLread_raw *3.4/4094.00)*1000;
            tempRead = dataA + randNumber;
            volRead = dataB + randNumber2;
       
  
//Particle.publish("sendToDemo", "{\"tempRead\":\"" + String(tempRead) + "\",\"volRead\":\"" + String(volRead) +"\"}", 60, PRIVATE); 
 
 snprintf(buf, sizeof (buf), "{\"tempRead\":\"%.2f\", \"volRead\":\"%.2f\"}", tempRead, volRead); 

 
 Particle.publish("sendToDemo", buf, PRIVATE);
  delay(1000);
    }
}

```

Google script:

```cpp

//1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";

// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}

function doPost(e){
   return handleResponse(e);
}

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000); // wait 30 seconds before conceding defeat.

  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);

    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [];

    var json_obj = JSON.parse(e.postData.contents); 
    
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(json_obj[headers[i]]);
      }
    }

    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

```

The Google script is bound to a spreadsheet called DEMO as an app called DEMO\_SCRIPT . The webhook I have done differently to @jjlee32, by using JSON as the request format and a custom JSON script to define the content. The following is an image of my webhook format:

 ![Particle%20WEBHOOK](https://us1.discourse-cdn.com/flex026/uploads/particle/original/3X/a/4/a41c20e4c0835b128cc85243aa191b44566b7287.png)

Please note - in my case the timestamp only showed as the date in column A - just needed to format the column to get date and time

 ![Date%20and%20Time](https://us1.discourse-cdn.com/flex026/uploads/particle/original/3X/7/6/760f1e070e2cf5e25510795d3ac194f6344b1304.png)

---

<div class="post-metadata">

**Author:** ![armor](https://sea2.discourse-cdn.com/flex026/user_avatar/community.particle.io/armor/32/18398_2.png) [@armor](https://community.particle.io/u/armor)\
**Post date:** [December 10, 2019, 8:20am UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/10 "2019-12-10T08:20:46Z")

</div>

@DRCO Having SYSTEM\_THREAD(ENABLED); at the start of your code would be a good idea. Also, you need to be careful about the data types when doing calculations (mixing int and float for data A and dataB. I am a bit rushed right now but will try your code later to see if I can help figure out the problem.

---

<div class="post-metadata">

**Author:** ![DRCO](https://sea2.discourse-cdn.com/flex026/user_avatar/community.particle.io/drco/32/5268_2.png) [@DRCO](https://community.particle.io/u/DRCO)\
**Post date:** [December 10, 2019, 9:21am UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/11 "2019-12-10T09:21:29Z")

</div>

Good advice about the data types, it was just test code and the double was to accommodate the snprintf() method without modding @ScruffR ’ s code - bit of an afterthought. Interested why , SYSTEM\_THREAD(ENABLED) would be an advantage - can only publish if cloud is connected, right? Other than that, the project is working fine now - give it a go and let me know what you think. Any improvements always appreciated.

---

<div class="post-metadata">

**Author:** ![rpique](https://sea2.discourse-cdn.com/flex026/user_avatar/community.particle.io/rpique/32/11549_2.png) [@rpique](https://community.particle.io/u/rpique)\
**Post date:** [July 5, 2020, 7:06pm UTC](https://community.particle.io/t/webhook-builder-to-google-sheets/43012/12 "2020-07-05T19:06:11Z")

</div>

I think you may accidentally left out the setup function when copying and pasting the google script. It works great after adding the setup function.

```auto
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

```