Google Script: Play Sound when a specific cell change the Value
Asked Answered
E

3

5

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:

  1. Play a sound when a cell in column H changes to "Call" on the sheet "Support".
  2. This function will need to run every 5min.
  3. 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.

Emulsion answered 30/11, 2016 at 6:21 Comment(0)
M
7

This 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:

Monro answered 30/11, 2016 at 13:20 Comment(6)
Great is Working... But I hava a question, the sidebar not clear when the sound end. Is possible close automatically when the sound end?Emulsion
No, the sidebar needs to stay open so it can keep checking column H with window.setTimeout(checkCalls, x). Maybe you could style the sidebar more and add more functionality so the user has more reasons to keep it open.Monro
Josh, I found this funtion google.script.host.close() but i can find the way to use it... The sound need 2 sec for run... I trying to find the way to execute google.script.host.close() after 5 sec... this funtion close the sidebarEmulsion
Use an event handler that triggers when the audio has finished playing: W3 Schools onendedMonro
I found the code... Is there any way that the audio sounds even though it is not in the browser flap where I have the spreadsheet open? Because it only sounds if I'm on the flap where I opened that spreadsheet.Emulsion
I don't see the app script code to connect to / trigger the javascript in the html function notify() { document.getElementById("call").play(); }Deckhand
B
2

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>
Bashuk answered 7/10, 2020 at 15:50 Comment(0)
P
0

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()
  }
Parvenu answered 12/4 at 20:58 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.