Google Sheets Integration documentation needs update

[JSchrempp]

I have tried to follow the steps given in this Particle documentation for Google Sheet integration

AN011 Publish to Google Sheets | Integrations

I found that the process at Google has changed substantially from the Particle documentation. As a start, Script Editor does not appear under the Tools menu.

I found many other problems with the documented way of doing this. Someone could try following the instructions and I think the issues will be obvious.

Jim

[rickkas7]

As part of the community integrations, it's not updated as frequently as regular documentation would be, but the script editor is available in the Extensions menu as Apps Script.

[JSchrempp]

There is some very important info missing, from what I know now. Since the Apps Script is not associated with any particular Google Sheet, the script now has to specifically reference the URL of the target sheet. Also, app deployment is quite different. There are a couple of key choices to make (that are not obvious to the noob) and the deployment of new app code also requires a few not obvious choices.

It's a great integration to use once you figure out how to do it. The current documentation is just a teaser. If that page was community editable then I'd make changes to it.

Jim

[JSchrempp]

Here is better app sample code.

function test() {
  var e = {};
  e.parameter = {};
  e.parameter.event = 'sheetTest1';
  e.parameter.data = 'Any Ki{nd & of te,st \r\n data "can ] go: here"';
  e.parameter.coreid = '1f0030001647ffffffffffff';
  e.parameter.published_at = new Date().toISOString();
  doPost(e);
}

function doGet(e) { 
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1SDjUns8Wz5dlvmfxxxxxxxxxxxxxxxxxxxx/edit?gid=0#gid=0");
  var sheet = ss.getSheetByName("NewData");
  addLogRow(e,sheet);
}

function doPost(e) { 
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1SDjUns8Wz5dlvmfxxxxxxxxxxxxxxxxxxxx/edit?gid=0#gid=0");
  var sheet = ss.getSheetByName("NewData"); 
  addLogRow(e, sheet);
}

function addLogRow(e, sheet) {

  // time from the Google cloud - useful for debugging
  var now = new Date();
  var timePST = Utilities.formatDate(now, "America/Los_Angeles", "yyyy-MM-dd' 'HH:mm:ss");

  try{
    // unpack parameters from Particle cloud integration 
    var ev = e.parameter.event;
    var coreid = e.parameter.coreid;
    var timeParticle = e.parameter.published_at;
    var deviceData = e.parameter.data;
    
    // add a row to the sheet
    sheet.appendRow([timePST, coreid, timeParticle, deviceData] );

  } catch(error) {  
    sheet.appendRow(["Error in GApp: " + error, "PostData: " + JSON.stringify(e.postData)]);
  }

  // keep the number of rows within bounds by deleting the oldest entries
  cleanUpSheet(sheet);  

  return 0;
}

function cleanUpSheet(sheet) {
  
  const MAX_ROWS = 3000;  // delete some rows if sheet has more than this
  const ROWS_TO_DELETE = 500;  // number of oldest rows to delete in a cleanup operation
  
  var lastRow = sheet.getLastRow();
  
  if(lastRow >= MAX_ROWS) {
    sheet.deleteRows(2, ROWS_TO_DELETE); // keep header row
  }
  
}

[sudipta.gupta]

I am also facing this problem.
I can not send my sensor data to google spreadsheet as documented.
Can anyone help me?

[JSchrempp]

Some things I found to make it work ...

1. Create a Google App
2. Paste the code above into it
3. Create a google sheet
4. Copy the URL of the sheet from your browser and paste it into the script in the places where the URL is
5. Now click Deploy; you get a pop up. Choose web app. Let it run as your user. Make it available to anyone with the URL.
6. Then the pop up will provide a URL copy that.
7. Paste the URL into your Particle integration (web hook)
8. Click Test on the integration and add some random data. See that it appears in your google sheet. Ta-da.

When you make changes to your Google app script, save it. Then click Deploy but select Manage Integrations. Click the pencil icon and in the top drop down box pick New Version. This will preserve your Google App URL.

I hope this helps.
Jim

[rickkas7]

Updated Publish to Google sheets community integration with the updated user interface for Google Apps Script.

[JSchrempp]

Wow, that looks great! I think there's a small typo. If I understand correctly, either the text or the screen shot is wrong.

Thank you for doing this! I learned a few new things.
Jim

[rickkas7]

The text was wrong as the console changed. It's now:

If you left the Device ID field in the Test Parameters window blank, column A will be api.
Column B is the date and time the event was received by the cloud.
Columns C and to the right have the data that you entered in the event data box (if it was valid JSON).

[JSchrempp]

Great. Yesterday I implemented the mapping of coreID to spreadsheet idea. Works great and simplifies our workflow (for our team of two!). Thanks very much for this.