.setNumberFormat在Google Apps脚本中不起作用 [英] .setNumberFormat is not working in Google Apps Script

问题描述

我的Google Apps脚本中包含以下代码，该代码从Google电子表格获取数据，第15行(最后一行)返回错误，提示 TypeError:percent1.setNumberFormats不是函数(第15行，文件(代码").我应该改变些什么来解决这个问题?

I have the following code in my Google Apps Script which gets data from my Google Spreadsheet, and row 15 (the last row) returns an error saying TypeError: percent1.setNumberFormats is not a function (line 15, file "Code"). What should I change to fix this?

我想做的是，我想以 0.00％

function notifySlack(message) {

  var url = 'https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxx/edit#gid=xxxxxxxxx';
  var spreadSheet = SpreadsheetApp.openByUrl(url);
  var allSheets = spreadSheet.getSheets();
  var theSheet = allSheets[1];
  var lastRow = theSheet.getLastRow();
  var lastColumn = theSheet.getLastColumn();
  var sheetData = theSheet.getSheetValues(1, 1, lastRow, lastColumn);
  
  var ymd = Utilities.formatDate(sheetData[44][0], 'Asia/Tokyo', 'yyyy-MM-dd');
  
  var data1 = sheetData[44][1];
  var percent1 = sheetData[44][1] / sheetData[43][1];
  var dod1 = percent1.setNumberFormat('0.00%');
}

推荐答案

修改点:

• 在脚本中，当 sheetData [44] [1] 和 sheetData [43] [1] 的值是数字时， percent1 var percent1的code>等于sheetData [44] [1]/sheetData [43] [1]; 是数字.当这些是字符串时， percent1 是 NaN .
• 而且， setNumberFormat 是Class Class Range的方法.参考

Modification points:

• In your script, when the values of sheetData[44][1] and sheetData[43][1] are the numbers, percent1 of var percent1 = sheetData[44][1] / sheetData[43][1]; is the number. When those are the string, percent1 is NaN.
• And, setNumberFormat is the method of Class Class Range. Ref

在这种情况下，您的脚本会发生错误.这就是您遇到问题的原因.不幸的是，从您的脚本来看，我无法理解您的目标的细节.因此，在这个答案中，我想为您的目标提出2种模式.

By these situation, your script occurs the error. This is the reason of your issue. Unfortunately, from your script, I couldn't understand about the detail of your goal. So in this answer, I would like to propose 2 patterns for your goal I guessed.

在此模式中，将 var percent1 = sheetData [44] [1]/sheetData [43] [1];的 percent1 的值修改为 0.00％.在这种情况下， dod1 的值将成为一个字符串.

In this pattern, the value of percent1 of var percent1 = sheetData[44][1] / sheetData[43][1]; is modified to 0.00%. In this case, the value of dod1 becomes a string.

var dod1 = percent1.setNumberFormat('0.00%');

至:

var dod1 = Utilities.formatString("%1.2f%", percent1 * 100);

模式2:

在此模式中，说明了使用 setNumberFormat 的方法.当您想要修改单元格"A1"的数字格式时，在活动工作表中，可以使用以下脚本.在这种情况下，单元"A1"的值等于0.可以用作数字.

Pattern 2:

In this pattern, the method for using setNumberFormat is explained. When you want to modify the number format of the cell "A1" in the active sheet, you can use the following script. In this case, the value of the cell "A1" can be used as the number.

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1");
range.setNumberFormat('0.00%');

参考文献:

• setNumberFormat(numberFormat)
• formatString(模板，args)

这篇关于.setNumberFormat在Google Apps脚本中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！