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)
}
From your question, this sample supposes as follows.
if (updatedfiles.length > 0) {
, it posts at only when there are update file.fileName
and fileURL
created at while
loop are variable each other. So only last file in files
retrieved by searchFiles()
is used for posting.var updatedfiles = [];
, it imports information of updated files to the array. it post them using "attachments": updatedfiles
as the payload.attachments
is https://api.slack.com/docs/message-attachments.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.