Configure Google Apps Script for Sheets to stop Slack post when no updates

Question

I have set up google sheets to monitor an a folder for updates. When a file is uploaded within the last 24 hours, it updates my google sheet (perfect). Then it messages the designated slack channel (perfect). I want this script to run every minute and only send an update to slack when a new file was uploaded in that 1 minute time frame. How do I update the code?!?

Problems

1) if no files updated, it sends a message to slack still saying undefined for the file URL problem message in slack

2) if multiple files uploaded, and the google sheet updates, slack only gets a message for one of the files. only one link sent to slack image

Thoughts

1) I could add a function for slack and create a hourly trigger to check the drive file for updates then a trigger for the slack function to go off 2) regardless if one solves it, I need to figure out the code so there is no slack message when there is no update.

/***************************************************
Script will send an slack notification to Slack Channel
when a file is uploaded to monitored Drive Upload folder. SDH 5.29.17
***************************************************/
function checkForChangedFiles() {

// edit this line below with the ID "XXXXXXXxxXXXwwerr0RSQ2ZlZms" of the folder you want to monitor for changes
var folderID = '"' + "XXXXX" + '"';  
var folderSearch = folderID + " " + "in parents";
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getActiveSheet();

// Get the spreadsheet slack channel
var channel = sheet.getRange("F1").getValue();

// Get the spreadsheet time zone
var timezone = ss.getSpreadsheetTimeZone();
var today     = new Date();

// Find files modified in the last 24 hours, Run script next day, and set below to 24 hours
// 60 * 1000 = 60 second
// 60* (60 * 1000) = 60 mins which is 1 hour
// 24* (60* (60 * 1000)) = 1 day which 24 hours
var oneDayAgo = new Date(today.getTime() - 1 * 60 * 1000);  
// var oneDayAgo = new Date(today.getTime() - 1 * 60 * 1000);  

var startTime = oneDayAgo.toISOString();

var search = '(trashed = true or trashed = false) and '+ folderSearch +' and (modifiedDate > "' + startTime + '")';   

var files  = DriveApp.searchFiles(search);

var row = "", count=0;

while( files.hasNext() ) {

var file = files.next();
var fileName = file.getName();
var fileURL  = file.getUrl();
var lastUpdated =  Utilities.formatDate(file.getLastUpdated(), timezone, "yyyy-MM-dd HH:mm");
var dateCreated =  Utilities.formatDate(file.getDateCreated(), timezone, "yyyy-MM-dd HH:mm")

row += "<li>" + lastUpdated + " <a href='" + fileURL + "'>" + fileName + "</a></li>";

sheet.appendRow([dateCreated, lastUpdated, fileName, fileURL]);

count++;


} 

// add function for SLACK? then I can have slack run for each update and the first function run daily :) 


var url = "https://hooks.slack.com/services/XXXX";

var payload = {
 "channel" : channel,
 "username" : "DriveUpload", // <-- optional parameter, use if you want to override default "robot" name 
 "text" : "Upload! *''"+fileName+"''* "+ fileURL, // <-- REQUIRED parameter
 "icon_emoji": ":robot_face:", // <-- optional parameter, use if you want to override default icon, 
 //"icon_url" : "http://image" // <-- optional parameter, use if you want to override default icon
}

sendToSlack_(url,payload)
}

function sendToSlack_(url,payload) {
var options =  {
"method" : "post",
"contentType" : "application/json",
"payload" : JSON.stringify(payload)
};
 return UrlFetchApp.fetch(url, options)
}

Answer 1

From your question, this sample supposes as follows.

1. When a file is uploaded within the last 24 hours, it updates my google sheet (perfect).
2. Then it messages the designated slack channel (perfect).

For Question 1

• Reason :
  • At your script, when it runs, it posts to Slack every time. So even if there no updated files, "undefined" is posted.
• Solution :
  • Using if (updatedfiles.length > 0) {, it posts at only when there are update file.
  • In this case, when there is no update, the data is not posted.

For Question 2

• Reason :
  • fileName and fileURL created at while loop are variable each other. So only last file in files retrieved by searchFiles() is used for posting.
• Solution :
  • Using var updatedfiles = [];, it imports information of updated files to the array. it post them using "attachments": updatedfiles as the payload.
  • Help document for attachments is https://api.slack.com/docs/message-attachments.

Modified Script

The modified script is as follows. In my environment, this payload of script works fine. But if this doesn't work at your environment, it may require a debug.

var updatedfiles = []; // <--- ##Added
while( files.hasNext() ) {
  var file = files.next();
  var fileName = file.getName();
  var fileURL  = file.getUrl();
  var lastUpdated =  Utilities.formatDate(file.getLastUpdated(), timezone, "yyyy-MM-dd HH:mm");
  var dateCreated =  Utilities.formatDate(file.getDateCreated(), timezone, "yyyy-MM-dd HH:mm")
  row += "<li>" + lastUpdated + " <a href='" + fileURL + "'>" + fileName + "</a></li>";
  sheet.appendRow([dateCreated, lastUpdated, fileName, fileURL]);
  count++;
  updatedfiles.push({"text": "Upload! *''"+fileName+"''* "+ fileURL}); // <--- ##Added
}
if (updatedfiles.length > 0) { // <--- ##Added
  var url = "https://hooks.slack.com/services/XXXX";
  var payload = {
    "channel" : channel,
    "username" : "DriveUpload",
    "attachments": updatedfiles, // <--- ##Added
    "icon_emoji": ":robot_face:",
  }
  sendToSlack_(url, payload);
}

It modified checkForChangedFiles(). Script above var row = "", count=0; has not been modified. So above script shows only the modified part. It showed the modified parts as <--- ##Added.

If I misunderstand your questions, I'm sorry.