时间戳

首页 > 解决方案 > （自定义乘法表）数值​​大于1.79769E+308无法正常显示

google-apps-script - （自定义乘法表）数值​​大于1.79769E+308无法正常显示

问题描述

我正在研究一个在单位数量之间转换的自定义函数。

它应该看起来确定 A1 中的单元格，并将其用作另一个选项卡中第一行的搜索键。一旦从上一个选项卡中找到与 A1 匹配的单元格，它就会将该数字记录为索引以返回“vlookup”函数

https://docs.google.com/spreadsheets/d/1DgdggqfKRiuKsJWYSiMKl57ZkpuCZDWxyZKV7T2zzHU/edit?usp=sharing

 function uConvert( quantity, sourceUnit, targetUnit )
{
  var accountName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,1).getValue(); //determine the account name to use in the horizontal search
  var spreadsheet = SpreadsheetApp.getActive();
  var tableSheet = spreadsheet.getSheetByName("Productivity Standards");
  var tableData = tableSheet.getDataRange().getValues();
  var numColumns = tableSheet.getLastColumn();
  var sourceSpot = 0;
  var targetSpot = 0;
  
  
  // searches across row 1 to find which column to perform the multiplication/division
  for (var accountSpot = 0; accountSpot<numColumns; ++accountSpot){ 
    if (tableData[0][accountSpot]==accountName){break};
    }
    
    
  for ( var tableIndex = 0; tableIndex < tableData.length; ++tableIndex )
  {  
    var unit = tableData[tableIndex][0];
    if ( unit == sourceUnit )
    {
      sourceSpot = tableIndex;
    }
    if ( unit == targetUnit )
    {
      targetSpot = tableIndex;
    }
  }
if ( sourceSpot == 0 || targetSpot == 0 )
  {
    return "One or more units not in table";  
  }
  
  var convertedAmount = 0;
  if ( sourceSpot > targetSpot )
  {
    convertedAmount = convertHigherToLower( tableData, sourceSpot, targetSpot, quantity, accountSpot ); 
  }

  else if ( sourceSpot < targetSpot )
  {
    convertedAmount = convertLowerToHigher( tableData, sourceSpot, targetSpot, quantity, accountSpot );
  }
  else
  {
    return quantity;
  }
  return convertedAmount;
}

function convertHigherToLower( data, startIndex, endIndex, startingAmount, columnToSearch )
{

  // we're decending so we're multiplying
  // ( 3 pallets x 20 cartons per pallet x 30 pieces per carton = 1800 )

  var convertedQuantity = startingAmount;

  for ( var dataIndex = startIndex; dataIndex > endIndex; --dataIndex )
  {
    convertedQuantity *= data[dataIndex][columnToSearch];     
  }

  return convertedQuantity;

}

function convertLowerToHigher( data, startIndex, endIndex, startingAmount, columnToSearch )
{

  // we're ascending so we're dividing
  // Assume:
  // 300 pieces is 10 cartons is 0.5 pallets
  // this will be a ratio
  // the ratio will be starting amount ( 300 ) divided by the
  // remainder of the conversion amounts of the table
  // starting from the next higher thing on the table ( startIndex + 1 )
  // to the end of table
  // 300 /  20 / 30 
  // 15 / 30
  // equals 0.5

  var conversionAmount = startingAmount;

  for ( var dataIndex = startIndex + 1; dataIndex <= endIndex; ++dataIndex ) 
  {
    conversionAmount /= data[dataIndex][columnToSearch]; 
  }

  return conversionAmount;

}

在示例中

数字！

错误：数值大于1.79769E+308，无法正常显示。

标签： google-apps-scriptgoogle-sheetsgoogle-api

解决方案

像这样在您的代码中提供一些错误检查可能是值得的，也许您可​​能知道其他人，因为您更熟悉自己的工作。由于您的数字很大，因此值得考虑除以零的可能性。

function uConvert( quantity, sourceUnit, targetUnit ) {
  if(quantity && sourceUnit && targetUnit) {
    var accountName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,1).getValue(); //determine the account name to use in the horizontal search
    var spreadsheet = SpreadsheetApp.getActive();
    var tableSheet = spreadsheet.getSheetByName("Productivity Standards");
    var tableData = tableSheet.getDataRange().getValues();
    var numColumns = tableSheet.getLastColumn();
    var sourceSpot = 0;
    var targetSpot = 0;
    for (var accountSpot = 0; accountSpot<numColumns; ++accountSpot){ 
      if (tableData[0][accountSpot]==accountName){break};
    }
    for(var tableIndex=0;tableIndex<tableData.length;tableIndex++) {  
      var unit=tableData[tableIndex][0];
      if(unit==sourceUnit) {
        sourceSpot=tableIndex;
      }
      if(unit==targetUnit)
      {
        targetSpot=tableIndex;
      }
    }
    if(sourceSpot==0||targetSpot==0) {
      return "One or more units not in table";  
    }
    var convertedAmount=0;
    if (sourceSpot>targetSpot){
      convertedAmount=convertHigherToLower(tableData,sourceSpot,targetSpot,quantity,accountSpot); 
    }else if(sourceSpot<targetSpot){
      convertedAmount=convertLowerToHigher(tableData,sourceSpot,targetSpot,quantity,accountSpot);
    }else{
      return quantity;
    }
    return convertedAmount;
  }else{
    throw("Invalid Inputs in uConvert");
  }
}

function convertHigherToLower( data, startIndex, endIndex, startingAmount, columnToSearch ) {
  if(data && startIndex>-1 && endIndex>-1 && startingAmount && columnToSearch) {
    var convertedQuantity = startingAmount;
    for (var dataIndex=startIndex;dataIndex>endIndex;dataIndex--) {
      convertedQuantity*=data[dataIndex][columnToSearch];     
    }
    return convertedQuantity;
  }else{
    throw("Invalid Inputs in convertHigherToLower");
  }
}

function convertLowerToHigher( data, startIndex, endIndex, startingAmount, columnToSearch ) {
  if(data && startIndex>-1 && endIndex>-1 && startingAmount && columnToSearch) {
    var conversionAmount = startingAmount;
    for(var dataIndex=startIndex + 1;dataIndex<=endIndex;++dataIndex) {
      if(data[dataIndex][columnToSearch]) {
        conversionAmount/=data[dataIndex][columnToSearch]; 
      }else{
        throw("Invalid Divisor in convertLowerToHigher");
      }
    }
    return conversionAmount;
  }else{
    throw("Invalid Inputs in convertLowerToHigher");
  }
}

推荐阅读