Play a sound based on a criteria linked with Googlefinance formulae on Google Sheets

Question

I have a list of companies in Google Sheets and I am pulling their stock market price with a Googlefinance formula (e.g. googlefinance("LON:AAL","price")). This formula automatically updates the prices without manual intervention.

I am trying to set up a script to play a sound based on a criteria linked with the current price of a company.

For example:

Cell M2 has the formula: googlefinance("LON:AAL","price") and the price is 1003

When googlefinance formulae is auto updated, the price changes to 1050. 1050 is above the threshold I set which is 1040. Therefore Google sheets plays a sound when the price is updated to 1050.

I'd like to set this up for a column of data which is all updated by googlefinance formulas. Any one of them meets the threshold criteria, I'd like to hear a sound.

Reusing the code in How can I play a sound as part of a triggered function

Here's where I am:

Set up the player as a sidebar and have it open when the sheet is opened

/**
 * @OnlyCurrentDoc  Limits the script to only accessing the current spreadsheet.
 */

var SIDEBAR_TITLE = 'Sidebar Musicbox';

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
  showSidebar();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a sidebar. The sidebar structure is described in the Sidebar.html
 * project file.
 */

function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('Sidebar')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle(SIDEBAR_TITLE);
  SpreadsheetApp.getUi().showSidebar(ui);
}

Check every minute if range values meet the criteria and if yes trigger sound alert (I have got a time driven every minute trigger set up for this and I don't mind if it plays repeatedly)

Having read further, I think this check needs to be done at SidebarJavascript code via setInterval and not here.

function checkrange() {
  // Trigger set up for every minute
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('23.05.2015');
  var value = ss.getRange("M2:M40").getValues();
  for (var i = 0; i < 39; i++) {
  if (value[i][0] < 0.005) {
  playSidebar();
  }
  } 
  }

If the approach above is correct, I need to define the playSidebar() function to tell the sidebar to start playing.

Here's the HTML of the sidebar player

<!-- Use a templated HTML printing scriptlet to import common stylesheet -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<div class="sidebar branding-below">
    <p>
    A little music for your enjoyment!
    </p>
    <audio id="player" controls >
      <source src="http://soundbible.com/mp3/Fire_pager-jason-1283464858.mp3" type="audio/mpeg">
      Your browser does not support the audio element.
    </audio>
    <div id="sidebar-status"></div>
</div>

<div class="sidebar bottom">
  <span class="gray branding-text">Docs Add-on Sound Demo</span>
</div>

When range criteria is fulfilled sidebar to play the sound.

Either via the playsidebar() function or via sidebarjavascript.html.

SidebarJavascript.html

// setInterval to check the range values every minute and play when the criteria is fulfilled. 
// No need for the checkrange() function above if the check needs to be done at sidebarjavascript.

Can you please help me find the code to tell sidebar player to start playing ?

Answer 1

its not possible to do what you want efficiently.

as you already found out, you can play a sound only from an html app and only a sidebar is capable of loading an html app inside sheets.

however, the sidebar can only be opened by a user action or when the sheet is first opened.

your only option is to always keep the sidebar open (open it from onOpen) and then have a setInterval (say every 20 seconds) that constantly checks the range for changes. you would need to remember the lastl values to prevent the sound from constantly sounding.

you can also use spreadsheetApp.toast if you want to also show something visual.

Answer 2

Here is a working code to achieve what you want (I know this thread is quite old but it could be useful...) It will play a tone each time you select a cell with a different value in it. Not very sexy but that part is easy to improve.

Note : I used part of the code from a post by Mogsdad for the polling process

code.gs

/**
 * @OnlyCurrentDoc  Limits the script to only accessing the current spreadsheet.
 */

var SIDEBAR_TITLE = 'Sidebar Musicbox';

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen() {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
  showSidebar();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall() {
  onOpen();
}

/**
 * Opens a sidebar. The sidebar structure is described in the Sidebar.html
 * project file.
 */

function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('sidebar')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle(SIDEBAR_TITLE);
  SpreadsheetApp.getUi().showSidebar(ui);
}

function checkChange(){
  var cell =  PropertiesService.getScriptProperties().getProperty('cell');
  var newCellContent = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  if(cell != newCellContent){
    PropertiesService.getScriptProperties().setProperty('cell',newCellContent);
    return 'play';
  }else{
    return 'do nothing';
  }
}

sidebar.html :

<div class="sidebar branding-below">
    <p>
    A little music for your enjoyment!
    </p>
    <audio id="player" controls >
      <source src="https://dl.dropboxusercontent.com/u/211279/beep.mp3" type="audio/mpeg">
      Your browser does not support the audio element.
    </audio>
    <div id="sidebar-status"></div>
</div>

<div class="sidebar bottom">
  <span class="gray branding-text">Docs Add-on Sound Demo</span>
</div>
<?!= HtmlService.createHtmlOutputFromFile('sidebarJS').getContent(); ?>

sidebarJS.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script>
  $(function() {
    // call this when opening
    poll();
  });

  function poll(interval) {
    console.log('polling');
    interval = interval || 1000;
    setTimeout(function() {
      google.script.run
        .withSuccessHandler(checkCell)
        .checkChange();
    }, interval);
  };

  function checkCell(cell) {
  console.log('checkChange '+cell);
    if (cell=='play') {
  console.log('play because val= '+cell);
  $('#player').trigger("play");
    }
    poll();
  }
  </script>