I have some issues about countdown timer with start/stop/reset command button on Google Sheets . how to write a script for it by using google apps script, and put in google sheets, command by using start/stop/reset button.

标签： google-apps-script google-sheets

2条回答

forever°为你锁心

2楼-- · 2019-08-20 10:31

A Javascript Timer for Gathering data off of a Spreadsheet

I built this as a test setup for one of my projects. Here's the datatimer.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
     <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <style>
      #my_block{border:2px solid black;background-color:rgba(0,150,255,0.2);padding:10px 10px 10px 10px;}
      #conv_block{border: 1px solid black;padding:10px 10px 10px 10px;}
      .bttn_block{padding:5px 5px 0px 0px;}
      .sndr_block {border:1px solid rgba(0,150,0,0.5);background-color:rgba(150,150,0,0.2);margin-bottom:2px;}
    </style>
  </head>
  <body>
  <form>
    <div id="my_block" class="block form-group">
      <div class="sndr_block">
        <div id="myClock"></div>
        <br />Timer Duration(minutes):
        <br /><input id="txt1" type="text" size="4" class="action"/>
        <select id="sel1" onChange="loadTxt('sel1','txt1');">
          <option value="90000">1.5</option>
          <option value="96000">1.6</option>
          <option value="102000">1.7</option>
          <option value="108000">1.8</option>
          <option value="114000">1.9</option>
          <option value="120000" selected>2.0</option>
          <option value="126000">2.1</option>
          <option value="132000">2.2</option>
          <option value="138000">2.3</option>
          <option value="144000">2.4</option>
        </select>
        <div id="cntdiv"></div>
        <br /><strong>Timer Controls</strong>
        <div class="bttn_block"><input type="button" value="Start" name="startShow" id="startShow" onClick="startmytimer();" class="red" /></div>
        <div class="bttn_block"><input type="button" value="Stop" name="stopTimer" id="stopTimer" class="red" /></div>
        <div class="bttn_block"><input type="button" value="Single Ping" name="changedata" id="chgData" class="red" onClick="changeData();" /></div>

      </div>
      <div id="btn-bar">
        <br /><input type="button" value="Exit" onClick="google.script.host.close();" class="green" />
      </div>
    </div>
  </form>
    <script>
    var idx=1;
    var myInterval='';
    var cnt=0;
      $(function() {
        $('#startTimer').click(startmytimer);
        $('#stopTimer').click(stopTimer);
        $('#txt1').val('120000');
        startTime();
      });

      function startTime(){
        var today = new Date();
        var h = today.getHours();
        var m = today.getMinutes();
        var s = today.getSeconds();
        m = checkTime(m);
        s = checkTime(s);
        document.getElementById('myClock').innerHTML =
        h + ":" + m + ":" + s;
        var t = setTimeout(startTime, 500);
      }

      function checkTime(i){
        if (i < 10) {i = "0" + i};  // add zero in front of numbers < 10
        return i;
      }

      function startmytimer(){
        document.getElementById('cntdiv').innerHTML='<strong>Timer Started:</strong> ' + document.getElementById('myClock').innerHTML;
        myInterval=setInterval(changeData, Number($('#txt1').val()));
      }

      function stopTimer(){
        document.getElementById('cntdiv').innerHTML='Timer Stopped';
        clearInterval(myInterval);
      }

      function loadTxt(from,to){
        document.getElementById(to).value = document.getElementById(from).value;
      }

      function changeData(){
        $('#txt1').css('background','#ffffcc');
        google.script.run
        .withSuccessHandler(updateDisplay)
        .changeData();
      }

      function updateDisplay(t){
        $('#txt1').css('background','#ffffff');
        document.getElementById('cntdiv').innerHTML='<strong>Timer Running:</strong> Count= ' + ++cnt + ' <strong>Time:</strong> ' + t;
      }

     console.log('My Code');
   </script>
  </body>
</html>

Here's the datachanger.gs file:

function changeData(){
  var ss=SpreadsheetApp.getActive();
  var sho=ss.getSheetByName('TradingTimes')
  var tmr=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "HH:mm:ss");
  var col2=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "E MMM dd,yyyy HH:mm:ss");
  var col3=new Date(col2).getTime();
  var col4=new Date(col2).getTime();
  var dA=[[col2,col3,col4]];
  sho.getRange('B2:D2').setValues(dA);
  return tmr;
}

function showTimerSideBar()
{
  var ui=HtmlService.createHtmlOutputFromFile('datatimer').setTitle('Javascript Trigger Generator');
  SpreadsheetApp.getUi().showSidebar(ui);
}

I was just watching the output from a sheet that had Google Finance cell functions in it. But you can call anything you want as often as you like.

Here's what the dialog looks like:

0人赞添加讨论(0) 举报

神经病院院长

3楼-- · 2019-08-20 10:50

You can attach actions to images in a google sheet. That way you can have a start, stop and reset buttons as images. When clicked the images can link to functions in the appscript which updates the time in single cell.

Below is the code, and here is a sample sheet . See the "attach action to images" document on how to trigger the buttons if needed.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1');

function  countDownSeconds(seconds, minutes){
  while (minutes >=0){   
     sheet.getRange(2,1).setValue(minutes);     
    while (seconds >= 0 ){     
      var m = sheet.getRange(2,1).getValue()
      var s = sheet.getRange(2,2).getValue()
      if (m === 'pausing' || s === 'pausing'|| m === 'reset' || s === 'reset' ) {
       return
      }      
      sheet.getRange(2,2).setValue(seconds); 
      SpreadsheetApp.flush();
      Utilities.sleep(1000)
      seconds --
    }    
    seconds = 59;
    minutes = sheet.getRange(2,1).getValue();
    minutes --
  }
}

function startTimer(){
   var minutes = sheet.getRange(2,1).getValue()
   var seconds = sheet.getRange(2,2).getValue()
   countDownSeconds(seconds, minutes)

}

function pause(){
   var minutes = sheet.getRange(2,1).getValue()
   var seconds = sheet.getRange(2,2).getValue()
   sheet.getRange(2,1).setValue('pausing');  
   sheet.getRange(2,2).setValue('pausing');   
   SpreadsheetApp.flush();
   Utilities.sleep(1000);
   sheet.getRange(2,1).setValue(minutes)
   sheet.getRange(2,2).setValue(seconds)
}

function reset(){
   sheet.getRange(2,1).setValue('reset');  
   sheet.getRange(2,2).setValue('reset');   
   SpreadsheetApp.flush();
   Utilities.sleep(1000);
   sheet.getRange(2,1).setValue(0)
   sheet.getRange(2,2).setValue(0)
}

0人赞添加讨论(0) 举报

Countdown timer start/stop/reset button with Apps

A Javascript Timer for Gathering data off of a Spreadsheet

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间