如何在单元格达到Google表格中的某个值时自动触发宏? [英] How can I get a macro to automatically trigger when a cell reaches a certain value in a Google Sheet?

问题描述

我需要的是我记录的一个名为SwitchHotSeat的宏，当单元格F3的值超过£1,000,000.00 0r 1000000或单元格F3的LEN超过该长度时触发.

What I need is for a macro I've recorded called SwitchHotSeat to trigger when the value of cell F3 goes above £1,000,000.00 0r 1000000 or the LEN of cell F3 goes over that length.

我可以在Excel中找到执行此操作的指南，但对于Google表格则找不到.下面只是我的宏的代码.

I can find guides for doing this in Excel, but not for Google Sheets. below is just the code for my macro.

function SwitchHotSeat() {
  var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('3:3').activate();

 spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
  spreadsheet.getActiveRangeList().setBackground('#ff0000')
  .setFontColor('#ffffff');
};

宏正常工作.我只需要一种在该单元格超过1000000时触发它的方法.

The macro works fine. I just need a way of triggering it when that cell goes over the 1000000.

推荐答案

以防万一它可以帮助其他遇到类似问题的人，我(通过记录)发现它正在返回一个对象，而不是之前的一个数字，因此最终，下面的代码运行良好.

Just in case it helps anyone else with a similar issue, I discovered (by logging) that it was returning an object, rather than a number before, so the following code worked fine in the end.

function onFormSubmit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
   var millionaire24 = ss.getSheetByName('Millionaire24.');
    var cellValue = millionaire24.getRange(3,6,1,1).getValues();
  Number(cellValue)
if(cellValue[0] >= 1000000)switchHotSeat();


}

这篇关于如何在单元格达到Google表格中的某个值时自动触发宏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！