Google脚本:返回值而不是公式 [英] Google scripting: return value instead of formula

问题描述

我制作了一个剧本:

function getWN8() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var startRow = 11;
  var queryString = Math.random();
  var dataRange = sheet.getRange(startRow, 3)
  var data = dataRange.getValues();
  var values = SpreadsheetApp.getActiveSheet().getDataRange().getValues()
  var input, cellFunction, range, row;


  for (var n=startRow-1; n < values.length;++n){
    input = values[n][0];
    row = values[n];
    cellFunction = '=INDEX(IMPORTHTML("http://en.wot-life.comeu/player/' + input + '", "table", 1),16,2)';

    for (var i = 0; i < row.length; ++i) {
        range = sheet.getRange((n+1), 2);
        range.setValue(cellFunction);
    }
  }
}

此脚本正在从第一列中读取值，将第一列中的值插入到http链接中，并将输出推送到第二列中的单元格中.但是，如果我在工作表中查看，我已经在单元格中显示了数值，但是它可能会威胁到公式.因此，我无法设置条件格式.

This script is reading values from first column, insert value from first column to http link and output is pushed into cells in 2nd column. But If I look in sheet, I have displayed numeric value in cell, but it is threated as an formula. Therefore I can't set conditional formatting.

因此，我要做的就是将值推入单元格而不是公式中.怎么做到呢 ?我不太擅长编写脚本或编程.

So all I need to do, is to push value to the cell instead of formula. How can it be done ? I'm not very skilled in scripting, or programming.

感谢您的帮助

伊沃(Ivo)

推荐答案

要将公式转换为值，您必须在上次迭代中添加一些内容，例如:

To turn the formula into a value you will have to add something to your last iteration like:

var val = range.getValue;
range.setValue(val);

不幸的是，我还没有找到一种使用电子表格公式直接计算和输入值的方法.要直接输入值，将需要执行与公式完全相同的数学运算的代码.

Unfortunately, I haven't found a way calculate and put in the value directly by using a spreadsheet formula. To put in the value directly, it would require a code that is executing exactly the same math as the formula.

这篇关于Google脚本:返回值而不是公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！