how to time how long a cell has a specific color?

2020-05-10 08:01发布

站内问答 / 后端开发
933 1 4

Picture of the cells- google sheet I have a cell in my google sheet that changes color base on a text, and it is refreshing every minute. I would like to have a timer tracking the number of minutes this cell is red.

As the color change over the day, is it possible to sum (or add up the minutes) every time the cell is red and have a total value per day?

I have no function for the timer. The information in "time" column are pulled from a website via xmlimport.

It is completely new area for me. Let me know if it's unclear. Thanks for your help

1条回答
一夜七次
2楼-- · 2020-05-10 08:21

A JavaScript Timer for Collecting Time and Color of A Single Cell

While this is probably not exactly what you want, I suspect it has all of the components for you to figure out how to get what you want.

datatimer.html

This is Javascript Timer that I've used several times for different things and I adapted it to this function for you to use as an example to help you realize your goal.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no"/>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.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:2px 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" style="font-size:20px;font-weight:bold;"></div>
        <br />Timer Duration(minutes):
        <br /><input id="txt1" type="text" size="4" class="action"/>
        <select id="sel1" onChange="loadTxt('sel1','txt1');">
        </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();changeData();" 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 class="bttn_block"><input type="button" value="Red" name="setA1Red" id="setRed" class="red" onClick="setA1('#ff0000');" /></div>
        <div class="bttn_block"><input type="button" value="Green" name="setA1Green" id="setGreen" class="green" onClick="setA1('#00ff00');" /></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() {
        var select = document.getElementById('sel1');
        select.options.length = 0; 
        for(var i=1;i<61;i++)
        {
          select.options[i-1] = new Option(i,i * 60000);
        }
        select.selectedIndex=0;
        $('#startTimer').click(startmytimer);
        $('#stopTimer').click(stopTimer);
        $('#txt1').val(String(select.options[select.selectedIndex].value));
        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 exportData() {
        google.script.run.saveData(cA);
      }

      function changeData(){
        $('#txt1').css('background','#ffffcc');
        google.script.run
        .withSuccessHandler(function(rObj){
          updateDisplay(rObj.timer);
          saveData({timer:rObj.timer,color:rObj.color});
          $('#txt1').css('background','#ffffff');
        })
        .changeData();
      }

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

      function setA1(color) {
        console.log(color);
        google.script.run.setA1(color);
      }

      function saveData(dObj) {
        google.script.run.saveData(dObj);
      }
     console.log('My Code');
   </script>
  </body>
</html>

Code.gs:

function onOpen(){
  SpreadsheetApp.getUi().createMenu('MyTools')
  .addItem('Show Sidebar', 'showTimerSideBar')
  .addToUi();
}

function showTimerSideBar()
{
  var ui=HtmlService.createHtmlOutputFromFile('datatimer').setTitle('Exercise Timer');
  SpreadsheetApp.getUi().showSidebar(ui);
}

function changeData(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('ColorChange');
  var rg=sh.getRange('A1');
  var color=rg.getBackground();
  var n=new Date();
  var tmr=Utilities.formatDate(n, Session.getScriptTimeZone(), "HH:mm:ss")
  var rObj={color:color,timer:tmr};
  ss.toast(Utilities.formatString('timer: %s color: %s', tmr,color));
  return rObj;
}

function saveData(dObj) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Data');
  var lr=sh.getLastRow();
  sh.getRange(lr+1,1).setValue(dObj.timer);
  sh.getRange(lr+1,2).setBackground(dObj.color);
}

function setA1(color) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('ColorChange');
  var rg=sh.getRange('A1');
  rg.setBackground(color);
}

An image of the timer module in the sidebar:

enter image description here

This is just a little test setup that checks on the color of cell A1 every minute or whatever you select. You can start the timer, stop the timer, do a single ping any time you want. The timers calls a function called changeData() and that generates a time string and reads the background color of ColorChange!A1. It the returns that information to the client and calls a client side function to save the data in a sheet named "Data".

This is what the Data sheets looks like after a few samples and single pings.

enter image description here

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)