0

I want to send information from Slack into a new row in a Google Sheet and include a timestamp indicating when that row was added.

Following Slack instructions on how to Send Information into a Google Sheet I have successfully added a row, but do not see how to send a timestamp along with it.

I found a Google Apps script from this Stackoverflow page to add a timestamp when a cell is edited:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 1 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
    }
  }
}

The script adds a timestamp when I manually enter data into the Google spreadsheet, but when I add a new row from Slack, no timestamp appears.

This Stackoverflow post is about a similar issue. It explains that there's a difference between pasting and editing, so I think my problem is related. But I still don't understand what exact changes to make in the script above, and I wasn't able to implement the corrected script on that page successfully either.

Can someone help me either

  • tweak the script above so it will work when I'm adding a row from Slack; or
  • figure out another way to send a timestamp from Slack as part of that row of data?

Thank you!

Milton Crandall
  • 1
  • 1
    `onEdit` trigger is not activated by formulas or script only by **user** edits. Only workarounds exist which solely depend on your scenario. – Marios Jan 16 '21 at 04:40

1 Answers1

0

Check the "Send submitted responces to a channel or to someone in a DM" checkbox.

Add this code:

function CheckEmail() {
  var ss = SpreadsheetApp.openById('SPREADSHEET_ID');
  var sheet = ss.getSheetByName('Sheet1');
  var range = sheet.getRange(sheet.getLastRow() + 1, 1);
  var mail = GmailApp.search('subject:mail-from-slack')[0];
  if (mail != null) {
    var msg = mail.getMessages()[0];
    var date = msg.getDate();
    range.setValues([[date]]);
    GmailApp.getMessageById(msg.getId()).moveToTrash();
  } else {
    Logger.log('No messages');
  }
}

Correct the subject to your own.

Set the clock trigger.

Triggers

Boris Baublys
  • 952
  • 10
  • 22
  • Thanks @boris, I will try this – Milton Crandall Jan 16 '21 at 19:45
  • @MiltonCrandall Have you tried it, did it work? – Boris Baublys Jan 25 '21 at 18:06
  • Thanks for following up @boris. I tried to implement this and got stuck on the emails coming out slack, because I had turned off notifications. So I found a more straightforward solution on r/GoogleAppsScript, which involved changing `onEdit` to `onChange`. Details at https://www.reddit.com/r/GoogleAppsScript/comments/l01amu/adding_a_timestamp_with_google_apps_script_when/gjr09w6/?utm_source=reddit&utm_medium=web2x&context=3 – Milton Crandall Jan 26 '21 at 23:09