Situation:
Example Spreadsheet
Sheet: Support
Column: H has the following function "=IF(D:D>0;IF($B$1>=$G:G;"Call";"In Time");" ")" that changes the value depending on the result.
Problem:
I need to:
- Play a sound when a cell in column H changes to "Call" on the sheet "Support".
- This function will need to run every 5min.
- Does the sound need to be uploaded to Drive or can I use a sound from a URL?
I will appreciate to anyone can help on it... I see a lot of code but I didn't understand very well.
Situation:
Example Spreadsheet
Sheet: Support
Column: H has the following function "=IF(D:D>0;IF($B$1>=$G:G;"Call";"In Time");" ")" that changes the value depending on the result.
Problem:
I need to:
- Play a sound when a cell in column H changes to "Call" on the sheet "Support".
- This function will need to run every 5min.
- Does the sound need to be uploaded to Drive or can I use a sound from a URL?
I will appreciate to anyone can help on it... I see a lot of code but I didn't understand very well.
Share Improve this question edited Nov 30, 2016 at 14:17 Joshua Dawson 62910 silver badges17 bronze badges asked Nov 30, 2016 at 6:21 LAD Service DeskLAD Service Desk 2896 gold badges14 silver badges27 bronze badges3 Answers
Reset to default 7This is a pretty tough problem, but it can be done with a sidebar that periodically polls the H column for changes.
Code.gs
// creates a custom menu when the spreadsheet is opened
function onOpen() {
var ui = SpreadsheetApp.getUi()
.createMenu('Call App')
.addItem('Open Call Notifier', 'openCallNotifier')
.addToUi();
// you could also open the call notifier sidebar when the spreadsheet opens
// if you find that more convenient
// openCallNotifier();
}
// opens the sidebar app
function openCallNotifier() {
// get the html from the file called "Page.html"
var html = HtmlService.createHtmlOutputFromFile('Page')
.setTitle("Call Notifier");
// open the sidebar
SpreadsheetApp.getUi()
.showSidebar(html);
}
// returns a list of values in column H
function getColumnH() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Support");
// get the values in column H and turn the rows into a single values
return sheet.getRange(1, 8, sheet.getLastRow(), 1).getValues().map(function (row) { return row[0]; });
}
Page.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<p id="message">Checking for calls...</p>
<audio id="call">
<source src="||a URL is best here||" type="audio/mp3">
Your browser does not support the audio element.
</audio>
<script>
var lastTime = []; // store the last result to track changes
function checkCalls() {
// This calls the "getColumnH" function on the server
// Then it waits for the results
// When it gets the results back from the server,
// it calls the callback function passed into withSuccessHandler
google.script.run.withSuccessHandler(function (columnH) {
for (var i = 0; i < columnH.length; i++) {
// if there's a difference and it's a call, notify the user
if (lastTime[i] !== columnH[i] && columnH[i] === "Call") {
notify();
}
}
// store results for next time
lastTime = columnH;
console.log(lastTime);
// poll again in x miliseconds
var x = 1000; // 1 second
window.setTimeout(checkCalls, x);
}).getColumnH();
}
function notify() {
document.getElementById("call").play();
}
window.onload = function () {
checkCalls();
}
</script>
</body>
</html>
Some sources to help:
- Sidebars and Dialogs
- Custom Menus
- Simple Trigger -
onOpen - `google.script.run.withSuccessHandler(callback).customFunction()
- Array.prototype.map
Recursively calling checkCalls() eventually led to errors, when I implemented the main answer given (which is mostly correct and really useful, so thank you!).
// Note: But the original implementation would work fine for a while - say 90 minutes - then crash. The call that would normally take 1 second would take 300 seconds, and Execution would Halt. It looks like it blew the stack by keeping on recursively calling itself. When moved to a single call of check() with proper exiting of the function, it then worked.
The console log in Chrome on running the JavaScript, said this: ERR_QUIC_PROTOCOL_ERROR.QUIC_TOO_MANY_RTOS 200
After much investigation, I worked out a better way of doing it... Which doesn't require recursion (and therefore won't blow the stack).
Remove this line: // window.setTimeout(checkCalls, 500);
And use something like this - at the end of your script:
// This function returns a Promise that resolves after "ms" Milliseconds
// The current best practice is to create a Promise...
function timer(ms) {
return new Promise(res => setTimeout(res, ms));
}
async function loopthis () { // We need to wrap the loop into an async function for the await call (to the Promise) to work. [From web: "An async function is a function declared with the async keyword. Async functions are instances of the AsyncFunction constructor, and the await keyword is permitted within them. The async and await keywords enable asynchronous, promise-based behavior to be written in a cleaner style, avoiding the need to explicitly configure promise chains."]
for (var i = 0; i >= 0; i++) {
console.log('Number of times function has been run: ' + i);
checkCalls();
await timer(3000);
}
}
window.onload = function () {
loopthis();
}
</script>
I wanted to implement this feature as well in my project. I used the idea of Joshua Dawson of using the sidebar for playing the audio. However, I was not convinced with the infinite loop idea of Rob Blakemore where a wait function is called every iteration. Instead, there is already a function implemented: setInterval See the documentation online for more details. Below is my code example one needs to add to the end of the section of the HTML file.
function runEverySecond() {
//This function checkCalls() is triggered every second
setInterval(function() {
checkCalls();
}, 1000); // 1000 milliseconds = 1 seconds
}
window.onload = function () {
runEverySecond()
}