Google Apps Script: 为什么我会收到“参数(字符串,数字)不匹配”的错误?

huangapple go评论67阅读模式
英文:

Google Apps Script: Why am I getting 'The parameters (String,number) don't match' error?

问题

Question: 我正在尝试通过Google Apps Script获取条件边框与不同的变量输入数据有所不同。但是,我一直收到相同的错误消息,无法确定问题出在哪里。在代码中我漏掉了什么?

  1. 我收到的错误消息如下:

    异常:参数(String,number)与 SpreadsheetApp.Spreadsheet.getRange 方法签名不匹配。

  2. 我编写的代码如下:

function myBorder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'))
  for (var i = 1; i < 20; i++) for (var j = 1; j < 20; j++) {
    if (spreadsheet.getRange(i, j).getValue().length > 0)
      spreadsheet.getRange(i, j).setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM)
    if (spreadsheet.getRange(i, j).getValue() == '공란')
      spreadsheet.getRange(i, j).setBorder(true, true, true, false, null, null, "black", SpreadsheetApp.BorderStyle.SOLID).setBackground("#E2E2E2")
  }
}
  1. 我尝试获取特定日期数据的特定单元格(失败)的尝试如下:
else if (Object.prototype.toString.call(val) == "[object Date]" && Date.val == '2999. 1. 1') {
  range.setBorder(true, false, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID).setBackground("#E2E2E2");
}
英文:

Question: I'm trying to get conditional borderline differ from varient input data via Google Apps Script.
But, I keep getting same Error message and can't tell what's the issue.
What do I miss in the code?

  1. Error message I got is below :

>Exception: The parameters (String,number) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange.

  1. Code I wrote is below :
    function myBorder() {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(&#39;Sheet1&#39;))
    for(var i=1; i &lt; 20 ; i++)for( j=1; j&lt;20; j++){
    if(spreadsheet.getRange(i,j).getValue().length&gt;0)
 spreadsheet.getRange(i,j).setBorder(true,true,true,true,null,null,&quot;black&quot;,SpreadsheetApp.BorderStyle.SOLID-MEDIUM)
    if(spreadsheet.getRange(i,j).getValue()==&#39;공란&#39;)
       spreadsheet.getRange(i,j).setBorder(true,true,true,false,null,null,&quot;black&quot;,SpreadsheetApp.BorderStyle.SOLID).setBackground(&quot;#E2E2E2&quot;)
  }}
  1. What i've tried to get specific cell with specific date data(failed)
else if (Object.prototype.toString.call(val) == &quot;[object Date]&quot; &amp;&amp; Date.val == &#39;2999. 1. 1&#39;) {
      range.setBorder(true, false, true, true, null, null, &quot;black&quot;, SpreadsheetApp.BorderStyle.SOLID).setBackground(&quot;#E2E2E2&quot;);}


</details>


# 答案1
**得分**: 1

使用 Spreadsheet 类对象的 .getRange() 时,它只接受 A1 表示法。您想要做的是在 Sheet 类对象上使用 .getRange(i, j)。在设置活动表单后添加这一行可以解决这个问题:

```javascript
spreadsheet = spreadsheet.getActiveSheet();

然而,这可以进一步简化。以下是两个完整函数的版本。首先,对于每种数据类型,使用不同的格式:

function myBorder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  for (i = 1; i < 20; i++) for (j = 1; j < 20; j++) {
    var range = spreadsheet.getRange(i, j);
    var val = range.getValue();
    if (Object.prototype.toString.call(val) == "[object Date]") {
      range.setBorder(true, true, true, true, null, null, "blue", SpreadsheetApp.BorderStyle.DOTTED).setBackground("mistyrose");
    } else if (typeof val == 'number') {
      range.setBorder(true, true, true, false, null, null, "green", SpreadsheetApp.BorderStyle.DASHED).setBackground("powderblue");
    } else if (val == '공란') {
      range.setBorder(true, true, true, false, null, null, "black", SpreadsheetApp.BorderStyle.SOLID).setBackground("#E2E2E2");
    } else if (typeof val == 'string' && val.length > 0) {
      range.setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    }
  }
}

其次,对于所有数据类型,使用一个格式:

function myBorder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  for (i = 1; i < 20; i++) for (j = 1; j < 20; j++) {
    var range = spreadsheet.getRange(i, j);
    var val = range.getValue();
    if (val == '공란') {
      range.setBorder(true, true, true, false, null, null, "black", SpreadsheetApp.BorderStyle.SOLID).setBackground("#E2E2E2");
    } else if (Object.prototype.toString.call(val) == "[object Date]" || typeof val == 'number' || (typeof val == 'string' && val.length > 0)) {
      range.setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    }
  }
}

为了提高效率,我们可以为每种值类型构建一个 RangeList 数组。为了将数字与货币分开(而不检查单元格格式,因为太慢),我们可以将显示值与货币字符数组进行比较。

还添加了一个用于查找特定日期的变量。目前,这会忽略 GMT 时区,因为在某一端忽略了夏令时。其他人可能更了解如何处理这个问题。

function myBorder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var specDate = new Date(2023, 2, 2);
  var rows = 20;
  var columns = 20;
  var array = spreadsheet.getRange(1, 1, rows, columns).getValues();
  var display = spreadsheet.getRange(1, 1, rows, columns).getDisplayValues();

  var currencies = ["Lek", "؋", "$", "ƒ", "₼", "৳", "Br", "BZ$", "$b", "KM", "P", "лв", "R$", "៛", "¥", "₡", "kn", "₱", "Kč", "kr", "RD$", "£", "€", "¢", "Q", "L", "Ft", "₹", "Rp", "﷼", "₪", "J$", "₩", "₭", "ден", "RM", "₨", "₮", "د.إ", "MT", "C$", "₦", "B/.", "Gs", "S/.", "zł", "lei", "₽", "Дин.", "S", "R", "CHF", "NT$", "฿", "TT$", "₺", "₴", "$U", "Bs", "₫", "Z$"];

  var spec = [];
  var date = [];
  var cur = [];
  var num = [];
  var blank = [];
  var string = [];

  for (i = 1; i < rows; i++)
    for (j = 1; j < columns; j++) {
      var val = array[i - 1][j - 1];
      var disp = display[i - 1][j - 1];
      var r1c1 = `R${i}C${j}`;
      if (Object.prototype.toString.call(val) == "[object Date]") {
        if (val.toString().split('GMT')[0] == specDate.toString().split('GMT')[0]) {
          spec.push(r1c1);
        } else date.push(r1c1);
      } else if (typeof val == 'number') {
        if (val != disp && currencies.some(e => new RegExp(e).test(disp))) {
          cur.push(r1c1);
        } else num.push(r1c1);
      } else if (typeof val == 'string' && val.length > 0) {
        if (val == '공란') {
          blank.push(r1c1);
        } else string.push(r1c1);
      }
    }

  if (spec.length) spreadsheet.getRangeList(spec).setBorder(true, true, true, true, null, null, "blue", SpreadsheetApp.BorderStyle.SOLID_THICK).setBackground("mistyrose");
  if (date.length) spreadsheet.getRangeList(date).setBorder(true, true, true, true, null, null, "blue", SpreadsheetApp.BorderStyle.DOTTED).setBackground("mistyrose");
  if (num.length) spreadsheet.getRangeList(num).setBorder(true, true, true, false, null, null, "green", SpreadsheetApp.BorderStyle.DASHED).setBackground("powderblue");
  if (cur.length) spreadsheet.getRangeList(cur).setBorder(true, true, true, false, null, null, "green", SpreadsheetApp.BorderStyle.DASHED).setBackground("cyan");
  if (blank.length) spreadsheet.getRangeList(blank).setBorder(true, true, true, false, null, null, "black", SpreadsheetApp.BorderStyle.SOLID).setBackground("#E2E2E2");
  if (string.length) spreadsheet.getRangeList(string).setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
英文:

When using .getRange() on a Spreadsheet class object, it only accepts A1 Notation. What you want to do is use .getRange(i, j) on a Sheet class object. Adding this line after setting the active sheet would fix this:

spreadsheet = spreadsheet.getActiveSheet();

However this can be further simplified. Here are two versions of the complete function. First, with different formats for each of your data types:

<!-- begin snippet: js hide: true console: true babel: false -->

<!-- language: lang-js -->

function myBorder() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&#39;Sheet1&#39;);
for (i = 1; i &lt; 20; i++) for (j = 1; j &lt; 20; j++) {
var range = spreadsheet.getRange(i, j);
var val = range.getValue();
if (Object.prototype.toString.call(val) == &quot;[object Date]&quot;) {
range.setBorder(true, true, true, true, null, null, &quot;blue&quot;, SpreadsheetApp.BorderStyle.DOTTED).setBackground(&quot;mistyrose&quot;);
} else if (typeof val == &#39;number&#39;) {
range.setBorder(true, true, true, false, null, null, &quot;green&quot;, SpreadsheetApp.BorderStyle.DASHED).setBackground(&quot;powderblue&quot;);
} else if (val == &#39;공란&#39;) {
range.setBorder(true, true, true, false, null, null, &quot;black&quot;, SpreadsheetApp.BorderStyle.SOLID).setBackground(&quot;#E2E2E2&quot;);
} else if (typeof val == &#39;string&#39; &amp;&amp; val.length &gt; 0) {
range.setBorder(true, true, true, true, null, null, &quot;black&quot;, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
}
}

<!-- end snippet -->

Second, with one format for all data types:

<!-- begin snippet: js hide: true console: true babel: false -->

<!-- language: lang-js -->

function myBorder() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&#39;Sheet1&#39;);
for (i = 1; i &lt; 20; i++) for (j = 1; j &lt; 20; j++) {
var range = spreadsheet.getRange(i, j);
var val = range.getValue();
if (val == &#39;공란&#39;) {
range.setBorder(true, true, true, false, null, null, &quot;black&quot;, SpreadsheetApp.BorderStyle.SOLID).setBackground(&quot;#E2E2E2&quot;);
} else if (Object.prototype.toString.call(val) == &quot;[object Date]&quot; || typeof val == &#39;number&#39; || typeof val == &#39;string&#39; &amp;&amp; val.length &gt; 0) {
range.setBorder(true, true, true, true, null, null, &quot;black&quot;, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
}
}

<!-- end snippet -->

To improve efficiency, we can build a RangeList array for each value type. To separate numbers from currencies without checking the cell formatting (too slow), we can test the display value against an array of currency characters.

There is also an added variable for looking for a specific date. Currently, this ignores the GMT timezone because Daylight Savings Time was being ignored on one end. Someone else might know better how to account for that.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

function myBorder() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&#39;Sheet1&#39;);
var specDate = new Date(2023, 2, 2);
var rows = 20;
var columns = 20;
var array = spreadsheet.getRange(1, 1, rows, columns).getValues();
var display = spreadsheet.getRange(1, 1, rows, columns).getDisplayValues();
var currencies = [&quot;Lek&quot;, &quot;؋&quot;, &quot;\\$&quot;, &quot;ƒ&quot;, &quot;₼&quot;, &quot;৳&quot;, &quot;Br&quot;, &quot;BZ\\$&quot;, &quot;\\$b&quot;, &quot;KM&quot;, &quot;P&quot;, &quot;лв&quot;, &quot;R\\$&quot;, &quot;៛&quot;, &quot;&#165;&quot;, &quot;₡&quot;, &quot;kn&quot;, &quot;₱&quot;, &quot;Kč&quot;, &quot;kr&quot;, &quot;RD\\$&quot;, &quot;&#163;&quot;, &quot;€&quot;, &quot;&#162;&quot;, &quot;Q&quot;, &quot;L&quot;, &quot;Ft&quot;, &quot;₹&quot;, &quot;Rp&quot;, &quot;﷼&quot;, &quot;₪&quot;, &quot;J\\$&quot;, &quot;₩&quot;, &quot;₭&quot;, &quot;ден&quot;, &quot;RM&quot;, &quot;₨&quot;, &quot;₮&quot;, &quot;د.إ&quot;, &quot;MT&quot;, &quot;C\\$&quot;, &quot;₦&quot;, &quot;B/\\.&quot;, &quot;Gs&quot;, &quot;S/\\.&quot;, &quot;zł&quot;, &quot;lei&quot;, &quot;₽&quot;, &quot;Дин.&quot;, &quot;S&quot;, &quot;R&quot;, &quot;CHF&quot;, &quot;NT\\$&quot;, &quot;฿&quot;, &quot;TT\\$&quot;, &quot;₺&quot;, &quot;₴&quot;, &quot;\\$U&quot;, &quot;Bs&quot;, &quot;₫&quot;, &quot;Z\\$&quot;];
var spec = [];
var date = [];
var cur = [];
var num = [];
var blank = [];
var string = [];
for (i = 1; i &lt; rows; i++)
for (j = 1; j &lt; columns; j++) {
var val = array[i - 1][j - 1];
var disp = display[i - 1][j - 1];
var r1c1 = `R${i}C${j}`;
if (Object.prototype.toString.call(val) == &quot;[object Date]&quot;) {
if (val.toString().split(&#39;GMT&#39;)[0] == specDate.toString().split(&#39;GMT&#39;)[0]) {
spec.push(r1c1);
} else date.push(r1c1);
} else if (typeof val == &#39;number&#39;) {
if (val != disp &amp;&amp; currencies.some(e =&gt; new RegExp(e).test(disp))) {
cur.push(r1c1);
} else num.push(r1c1);
} else if (typeof val == &#39;string&#39; &amp;&amp; val.length &gt; 0) {
if (val == &#39;공란&#39;) {
blank.push(r1c1);
} else string.push(r1c1);
}
}
if (spec.length) spreadsheet.getRangeList(spec).setBorder(true, true, true, true, null, null, &quot;blue&quot;, SpreadsheetApp.BorderStyle.SOLID_THICK).setBackground(&quot;mistyrose&quot;);
if (date.length) spreadsheet.getRangeList(date).setBorder(true, true, true, true, null, null, &quot;blue&quot;, SpreadsheetApp.BorderStyle.DOTTED).setBackground(&quot;mistyrose&quot;);
if (num.length) spreadsheet.getRangeList(num).setBorder(true, true, true, false, null, null, &quot;green&quot;, SpreadsheetApp.BorderStyle.DASHED).setBackground(&quot;powderblue&quot;);
if (cur.length) spreadsheet.getRangeList(cur).setBorder(true, true, true, false, null, null, &quot;green&quot;, SpreadsheetApp.BorderStyle.DASHED).setBackground(&quot;cyan&quot;);
if (blank.length) spreadsheet.getRangeList(blank).setBorder(true, true, true, false, null, null, &quot;black&quot;, SpreadsheetApp.BorderStyle.SOLID).setBackground(&quot;#E2E2E2&quot;);
if (string.length) spreadsheet.getRangeList(string).setBorder(true, true, true, true, null, null, &quot;black&quot;, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}

<!-- end snippet -->

huangapple
  • 本文由 发表于 2023年5月30日 12:03:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76361547.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定