如何将单元格中的空值替换为另一个单元格中的值？

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

how can I replace an empty value in a cell by a value from an other cell?

问题

I can help you with the translations. Here are the translated code parts:

  1. 我想要做三件简单的事情：
  2. 1/ 检查列N中的单元格是否为空。
  3. 2/ 如果是空的，我想要将单元格N的值设置为同一行中单元格Q的值。
  4. 3/ 我想要将N单元格的颜色变成红色。
  6. 我面临两个问题：
  7. 1/ A1.createTextFinder("") 不接受("") 作为空单元格的值描述。
  8. 2/ 我不知道如何从与N空单元格相同行的列Q调用值。
  10. 这是我找到并开始修改的代码：
  12. function STARTDATE() {
  13.   var PROsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("0_LISTE PROJETS");
  15.   var A1 = PROsheet.getRange("N3:N");
  16.   A1.createTextFinder("").replaceAllWith("Q");
  17. }

我还在不同的电子表格上使用了一个代码来删除列，当列A有一个空单元格时。我想这个代码可以很容易地从“删除行”修改为“复制来自其他行的值”，但迄今为止我还没有能够理解它是如何工作的...

如果可能的话，我希望解决方案基于这个代码，而不是另一个，因为我认为它可以帮助我更深入地理解基于相同类型的代码的不同功能。

代码本身：

  1. function myFunction() {
  2.   
  3.   var PLsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PLANNING_DATA");
  4.   
  5.   let rows = PLsheet.getDataRange();
  6.   let numRows = rows.getNumRows();
  7.   let valuesdelete = rows.getValues();
  9.   let rowsDeleted = 0;
  10.   for (var i = 0; i <= numRows - 1; i++) {
  11.     let row = valuesdelete[i];
  12.     if (row[0] == "")
  13.     {
  14.       PLsheet.deleteRow((parseInt(i)+1) - rowsDeleted);
  15.       rowsDeleted++;
  16.     }
  17.   }
  18. }

希望这对您有所帮助！

英文:

I Would like to do 3 simple things :
1/ check if a cell in the column N is empty.
2/ If it is, I would like to set in the cell N the value of the cell Q of the same line
3/ I wan't to turn this N cell into red

I'am facing two problems :
1/ A1.createTextFinder("") wont accept ("") as a value describing an empty cell
2/ I don't know how to call the value of the column Q from the same line as the N empty cell.

here is the code I find and start to modify

  1. function STARTDATE() {
  2.   var PROsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&quot;0_LISTE PROJETS&quot;);
  3.  
  5.   var A1 = PROsheet.getRange(&quot;N3:N&quot;);
  6.   A1.createTextFinder(&quot;&quot;).replaceAllWith(&quot;Q&quot;);
  7. }

I also use on a different spreadsheet a code to delete line when the column A have an empty cell. I guess this code can be easy to modify from "delete line" to "copy the value from an other row", but I haven't been able to understand how it works so far...
if it is possible, I would like the solution to be based on this code more them on the other one, because I think it can help me to go deeper in my understanding to see different fonction based on the same type of code

the code itself :

  1. function myFunction() {
  2.   
  3.   var PLsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&quot;PLANNING_DATA&quot;);
  4.   
  6.   let rows = PLsheet.getDataRange();
  7.   let numRows = rows.getNumRows();
  8.   let valuesdelete = rows.getValues();
  10.   let rowsDeleted = 0;
  11.   for (var i = 0; i &lt;= numRows - 1; i++) {
  12.   let row = valuesdelete[i];
  13.   if (row[0] == &quot;&quot;)
  15.     {
  16.       PLsheet.deleteRow((parseInt(i)+1) - rowsDeleted);
  17.     rowsDeleted++;
  18.     }
  19.   }
  22. }

thanks for your help !

答案1

得分: 3

Sure, here's the translated code part:

  1. function replaceMTSinNwithvalueInQ() {   
  2.   const ss = SpreadsheetApp.getActive();
  3.   const sh = ss.getSheetByName("PLANNING_DATA");
  4.   const vs = sh.getRange(2, 14, sh.getLastRow() - 1, 4).getValues();
  5.   let bs = sh.getRange(2, 14, sh.getLastRow() - 1).getBackgrounds();
  6.   let o = vs.map((r, i) => {
  7.     if (r[0] === "") {
  8.       bs[i][0] = "#ff0000"; // 设置背景颜色为红色
  9.       return [r[3]]; // 设置值
  10.     } else {
  11.       return [r[0]]; // 设置值
  12.     }
  13.   });
  14.   sh.getRange(2, 14, o.length, o[0].length).setValues(o).setBackgrounds(bs);
  15. }

Please note that I've translated the code and comments inside it, while keeping the code structure intact.

英文: 
  1. function replaceMTSinNwithvalueInQ() {   
  2.   const ss = SpreadsheetApp.getActive();
  3.   const sh = ss.getSheetByName(&quot;PLANNING_DATA&quot;);
  4.   const vs = sh.getRange(2,14,sh.getLastRow() - 1,4).getValues();
  5.   let bs = sh.getRange(2,14,sh.getLastRow() - 1).getBackgrounds();
  6.   let o = vs.map((r,i) =&gt; {
  7.     if(r[0] === &quot;&quot;) {
  8.       bs[i][0] = &quot;#ff0000&quot;;//color backgrounds in red
  9.       return [r[3]];//set value 
  10.     } else {
  11.       return [r[0]];//set value
  12.     }
  13.   })
  14.   sh.getRange(2,14,o.length,o[0].length).setValues(o).setBackgrounds(bs);
  15. }

huangapple
  • 本文由 发表于 2023年4月13日 23:51:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/76007443.html
  • google-apps-script
  • google-sheets
匿名

发表评论

匿名网友

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

确定