I am trying to run the code of this post, :
Google Script: Play Sound when a specific cell change the Value
with the recommendation of Rob Blakemore
Could someone pass me a sheet that is working with this code?
I have tried to test this code in a sheet and it does not work for me.
it only sends me the message: Checking for calls...
I send you the link of my sheet:
https://docs.google.com/spreadsheets/d/1rdm0zmZMherO8IspDb3Ce9n0j9tLYFYtM9V8fIX8uV4/edit#gid=0
this is the code of the appscript:
Code:
// 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:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<p id="message">Checking for calls...</p>
<audio id="call">
<!-- <source src="http://docs.google.com/uc?export=open&id=1CfWaMX6wfJSSdp-u_rU0bkRpwNmXWpVu/view?usp=sharing" type="audio/mp3"> -->
<source src="http://docs.google.com/uc?export=open&id=1CfWaMX6wfJSSdp-u_rU0bkRpwNmXWpVu/view?usp=sharing" 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();
}
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>
</body>
</html>
