pass in spreadsheet id?

[zenmanenergy]

It seems to me that we could improve the google script by passing in the spreadsheet id.

Instead of putting this at the top of the script... var SS = SpreadsheetApp.openById('1KAU0mzowlWnIuYE0_nVzv-iywkebTjM4nKtrlA-z70F'); var sheet = SS.getSheetByName('Sheet1');

Move it down to line 63 like this: if (parsedData !== undefined){ var SS = SpreadsheetApp.openById(parsedData.spreadsheet_id); var sheet = SS.getSheetByName(parsedData.sheet_name);

Then in the .ino file have both the GscriptId and a spreadsheet_id. Finally add the spreadsheet_id to the payload.

Seems like the benefit to this is you could have a generic googlescript file. I haven't tested this idea yet.

[bigFin]

Good call, I'm going to implement this meow.

[bigFin]

I just got around to this and it works as expected.

[churmate]

Hi bigFin, Does sheet_name allow you to choose which sheet within the spreadsheet, the values are written to? If so, could you please show the snippets of code you used to achieve this?

I have six Arduino nodes measuring temperature. I am trying to write each node's temperature to each node's sheet eg I have tabs Node1,,, Node6. I have tried to get this to work, but my values aren't been written to the sheet I expected. Thank you for your help.

[bigFin]

Yo @churmate

In order to choose the sheet, add a variable to the String url in your Arduino code as well as your Google App Script to identify and handle each Node.

Arduino code: Assign seperate hostName to each device const char* hostName = "Node1"; Add hostName to the url String url = String("script.google.com/macros/s/") + GScriptId + "/exec?hostName=" + hostName;

Google App Script: accept hostName var hostName = e.parameter.hostName; select the sheet var sheet = ss.getSheetByName(hostName); This will use the unique ID from each device to select a spreadsheet with the same name. Then you can read/write as you wish.

Cheers, Fin

[churmate]

Thank you Fin, I appreciate your help and clear explanation. I now think my strange results were due to the line below.
String payload_base = "{\"command\": \"appendRow\", \"sheet_name\": \"Sheet1\", \"values\": "; By changing to Sheet1 to Node1, 2, 3, etc it now works reliably.
All the best. Bruce

[bigFin]

Right on, glad I could help.