setValue在范围内，如果没有在其他范围内 [英] setValue in range if nothing in an other range

问题描述

在Google Spreadsheet中，我使用脚本编辑器在X2:D5之间的一列中插入Xpath.

In Google Spreadsheet, I use script editor to insert an Xpath in one column between D2:D5.

function untitle() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getRange('D2:D5');
  var cell = '=IMPORTXML(C2, "/html/body/div[2]")'; 
  range.setValue(cell);
};

C2包含网站的地址.

C2 include the address of the website.

我想在E2:E5之间进行同样的思考，如果单元格D2:D5中什么都没有

I would like to make the same think between E2:E5 if there is nothing in the cell D2:D5

我该怎么办?

编辑我:

我将代码与xpath一起使用以获取D2:D5中的信息

I use the code with xpath to get information in D2:D5

如果代码获得#N/A(或什么都没有)，我在E2:E5中使用第二个xpath

If the code get #N/A (or nothing) I use a second xpath in E2:E5

简而言之，我只想在D行中什么也没得到的情况下使用第二个xpath.

To be more lite, I would like to use the second xpath only if I get nothing in D row.

现在，所以我使用下面的代码，但是它检查了所有内容，所以效果不好.

Now, So I use the code below but it check everything so it's not good.

function untitle() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getRange('D2:D5');
  var range2 = sheet.getRange('E2:E5');
  var cell = '=IMPORTXML(C2, "/html/body/div[2]")'; 
  var cell2 = '=IMPORTXML(C2, "/html/body/div[3]")';
  range.setValue(cell);
  range2.setValue(cell2);
};

我想使用两个功能.

• 第一个，要在D2:D5中获取信息
• 第二，如果在D2:D5中结果为空(#N/A)，则在E2:E5中获取信息

推荐答案

• 如果"D2:D5"的单元格具有值和/或公式，则要将公式放入"D2:D5".
• 如果"D2:D5"的单元格中没有值和/或公式，则要将公式设置为"E2:E5".

如果我对您的问题的理解是正确的，那么此修改如何?我认为您的情况有几个答案.因此，请将此视为其中之一.

If my understanding for your question is correct, how about this modification? I think that there are several answers for your situation. So please think of this as one of them.

修改后的脚本的流程如下.

The flow of modified script is as follows.

1. 从单元格"D2:D5"中检索值和公式.
2. 如果单元格中有值或公式，请将公式放入"D2:D5".
3. 如果单元格中没有值或公式，请使用offset()将公式放入"E2:E5".
1. Retrieve values and formulas from the cells "D2:D5".
2. If there are the values or formulas in the cells, put the formulas to "D2:D5".
3. If there are no values or formulas in the cells, put the formulas to "E2:E5" using offset().

修改后的脚本:

请进行如下修改.

Modified script:

Please modify as follows.

range.setValue(cell);

到:

var values = range.getValues().some(function(e) {return e[0]});
var formulas = range.getFormulas().some(function(e) {return e[0]});
if (values || formulas) {
  range.setValue(cell);
} else {
  range.offset(0, 1).setValue(cell);
}

注意:

• 如果要在"D2:D5"中只有公式的情况下将公式放入"E2:E5"，请从脚本中删除formulas.

Note:

• If you want to put the formula to "E2:E5" when there are only formulas in the "D2:D5", please remove formulas from the script.
• Array.prototype.some()
• offset(rowOffset，columnOffset)
• Array.prototype.some()
• offset(rowOffset, columnOffset)

如果我误解了您的问题，请告诉我.我想修改它.

If I misunderstand your question, please tell me. I would like to modify it.

1. 将cell的公式放入"D2:D5".
2. 检索公式的结果.
3. 从检索结果中将cell2的公式放入"E2:E5".
   • var cell2 = '=IMPORTXML(C2, "/html/body/div[3]")';
   • 在这种情况下，当"D2:D5"的结果是"#N/A"或""时，请输入cell2的公式.
1. Put the formula of cell to "D2:D5".
2. Retrieve the results of the formulas.
3. Put the formula of cell2 to "E2:E5" from the retrieved results.
   • var cell2 = '=IMPORTXML(C2, "/html/body/div[3]")';
   • In this case, when the results of "D2:D5" are "#N/A" or "", put the formula of cell2.

如果我对您想要的内容的理解是正确的，那么修改后的脚本又如何呢?该脚本反映了以上流程.

If my understanding for what you want is correct, how about this modified script? This script reflect above flow.

function untitle() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getRange('D2:D5');
  var range2 = sheet.getRange('E2:E5');
  var cell = '=IMPORTXML(C2, "/html/body/div[2]")';
  range.setFormula(cell);

  var values;
  while (r != 4) {
    values = range.getValues();
    var r = values.filter(function(e) {return e[0]}).length;
  }
  var res = values.map(function(e, i) {return e[0] == "#N/A" || e[0] == "" ? ['=IMPORTXML(C' + (i + 2) + ', "/html/body/div[3]")'] : [""]});
  range2.setFormulas(res);
}

这篇关于setValue在范围内，如果没有在其他范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！