Google Script:当特定单元格更改值时播放声音 [英] Google Script: Play Sound when a specific cell change the Value

查看：25 发布时间：2021/12/15 22:54:00 javascript audio google-apps-script google-sheets

本文介绍了Google Script:当特定单元格更改值时播放声音的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

情况:

示例电子表格

<块引用>

表:支持
列:H有如下函数"=IF(D:D>0;IF($B$1>=$G:G;"Call";"In Time");" ")"根据结果改变值.

问题:

我需要:

<块引用>

当 H 列中的单元格更改为支持"表上的呼叫"时播放声音.
此功能需要每 5 分钟运行一次.
声音需要上传到云端硬盘还是我可以使用来自 URL 的声音?

我将不胜感激，任何人都可以提供帮助......我看到了很多代码，但我不太明白.

解决方案

这是一个相当棘手的问题，但可以通过定期轮询 H 列以了解更改的侧边栏来解决.

Code.gs

//打开电子表格时创建自定义菜单函数 onOpen() {var ui = SpreadsheetApp.getUi().createMenu('调用应用程序').addItem('打开调用通知程序', 'openCallNotifier').addToUi();//您还可以在电子表格打开时打开呼叫通知器侧边栏//如果你觉得这样更方便//openCallNotifier();}//打开侧边栏应用函数 openCallNotifier() {//从名为Page.html"的文件中获取 htmlvar html = HtmlService.createHtmlOutputFromFile('Page').setTitle("呼叫通知");//打开侧边栏电子表格App.getUi().showSidebar(html);}//返回列 H 中的值列表函数 getColumnH() {var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Support");//获取列 H 中的值并将行转换为单个值return sheet.getRange(1, 8, sheet.getLastRow(), 1).getValues().map(function (row) { return row[0]; });}

Page.html

<头><base target="_top"><身体><p id="message">正在检查呼叫...</p><audio id="通话"><source src="||网址最好在这里||"类型=音频/mp3">您的浏览器不支持音频元素.<脚本>var lastTime = [];//存储最后的结果以跟踪变化函数检查调用(){//这会调用服务器上的getColumnH"函数//然后等待结果//当它从服务器返回结果时，//它调用传入 withSuccessHandler 的回调函数google.script.run.withSuccessHandler(function (columnH) {for (var i = 0; i < columnH.length; i++) {//如果有差异并且是调用，则通知用户if (lastTime[i] !== columnH[i] && columnH[i] === "Call") {通知();}}//存储结果以备下次使用lastTime = columnH;控制台日志(上次)；//在 x 毫秒内再次轮询无功x = 1000;//1秒window.setTimeout(checkCalls, x);}).getColumnH();}函数通知(){document.getElementById("call").play();}window.onload = 函数 () {检查调用()；}

一些可以提供帮助的来源:

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.

解决方案

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:

这篇关于Google Script:当特定单元格更改值时播放声音的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

Google Script:当特定单元格更改值时播放声音 [英] Google Script: Play Sound when a specific cell change the Value

问题描述

相关文章

前端开发最新文章

热门教程

热门工具

登录关闭

Google Script:当特定单元格更改值时播放声音 [英] Google Script: Play Sound when a specific cell change the Value

问题描述

相关文章

前端开发最新文章

热门教程

热门工具

登录 关闭

登录关闭