如果满足条件，则将单元格的列增加1 [英] Increment column of cells by 1 if a condition is met

问题描述

我正在尝试创建一个宏，该宏会将工作表的C:5列更改为C:bottom.

I am trying to make a macro that will change columns C:5 to C:bottom of the sheet.

如果左侧的单元格(末尾为B:5)等于字符串"TRUE"，则这些单元格应增加1.

These cells should be incremented by 1 if the cell to the left (B:5 to end) is equal to the string 'TRUE'.

这是我到目前为止所拥有的，但条件尚未得到满足.我也不知道将其应用于整个专栏的最佳方法.我应该做一个for循环吗?

Here is what I have so far but the condition is not being met. I also don't know the best way to apply this to the whole column. Should I do a for loop?

这是我到目前为止所拥有的

Here is what I have so far

function increment() {
  var spreadsheet = SpreadsheetApp.getActive();
  var reocurring = spreadsheet.getRange("B5").getValue().toString();

  if (reocurring == 'TRUE')
  {
    var value = spreadsheet.getRange('C5').getValue();
    spreadsheet.getRange('C5').setValue(value + 1);
  }
};

推荐答案

问题:

您的if语句条件未得到满足.这是因为getValue()返回具有关联类型的值 .您的"TRUE"值将被识别为布尔值，而不是您的if语句所要查找的字符串.您正在使用toString()，但这会将值更改为'true'而不是'TRUE'，因此您的if语句条件仍然不满足.

Issue:

Your if statement condition isn't being met. This is because getValue() returns values with their associated type. Your "TRUE" value is being recognized as a boolean, not string like your if statement is looking for. You are using toString() but this changes the value to 'true' rather than 'TRUE', so your if statement condition still is not met.

有几种解决方法:

1. 更改您的if语句以查找布尔值:

if (reocurring === true)

将值强制设置为字符串和大写以确保满足您的条件:

Force the value to string and upper case to make sure your condition is met:

var reocurring = spreadsheet.getRange("B5").getValue().toString().toUpperCase();

使用getDisplayValue()从头开始返回字符串:

Use getDisplayValue() to return a string from the beginning:

var reocurring = spreadsheet.getRange("B5").getDisplayValue();

---

浏览表格:

由于如果要针对大型数据集运行脚本，这可能是一个运行时间很长的脚本，所以最好在此处使用数组.我已尝试对代码进行注释，以便于理解，但如果您有任何疑问，请告诉我.

---

Looping through the sheet:

Since this could be quite a long-running script if you're running it for a large data set, it's best to work with arrays here. I've tried to comment the code so it's a little easier to understand but if you have any questions please let me know.

function increment() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();

  //get array values in columns B and C starting from row 5
  var array = spreadsheet.getRange(5, 2, spreadsheet.getLastRow(), 2).getValues();

  //loop through array
  for (i = 0; i < array.length; i++) {
    if (array[i][0] === true) {
      //increment value in row C by 1
      array[i][1] = array[i][1] + 1;
    }
  }
  //set new incremented values
  spreadsheet.getRange(5, 2, array.length, array[0].length).setValues(array);
}

---

参考文献:

• getValues()
• setValues()
• JavaScript数组
• JavaScript数据类型

---

References:

• getValues()
• setValues()
• JavaScript Arrays
• JavaScript Data Types

这篇关于如果满足条件，则将单元格的列增加1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！