如果列包含文本，则在列中添加复选框 Google Sheets 应用脚本。

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

If column contains text add a checkbox in column apps script google sheets

问题

在范围 K80:K94 中，如果在 K 列中有数值，我想要在 M 列中添加复选框。例如，如果在 K80 和 K81 中有数值，那么在 M80 和 M81 中会有复选框。我觉得这应该是相当直接的，然而我尝试了几种不同的选项，包括使用 IsBlank()，但似乎都不起作用。

  1. var datavalues = dhs.getRange("K80:K94").getValues();
  2. var data_leng = datavalues.length;
  3. for(var i=0; i<data_leng; i++) {
  4.   if(datavalues[i][0].length != 0) {
  5.     dhs.getRange(i+1,14).insertCheckboxes();
  6.   } 
  7. }
英文:

I am copying data from a spreadsheet titled after the specific month and placing it in my main spreadsheet. I have successfully copied the data into range K80:K94 on my Daily Hub sheet.

In range K80:K94 I now want to add a checkbox in column M if there is a value in column K. For example if there is a value in K80 and K81 there would be a checkbox in M80 and M81. I feel like this should be fairly straightforward, however I have tried a few different options including using IsBlank() and nothing seems to be working.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

  1. function dailyhubhabits() {
  3.   
  5. var montha = new Array(12);
  6. montha[0] = &quot;JANUARY&quot;;
  7. montha[1] = &quot;FEBRUARY&quot;;
  8. montha[2] = &quot;MARCH&quot;;
  9. montha[3] = &quot;APRIL&quot;;
  10. montha[4] = &quot;MAY&quot;;
  11. montha[5] = &quot;JUNE&quot;;
  12. montha[6] = &quot;JULY&quot;;
  13. montha[7] = &quot;AUGUST&quot;;
  14. montha[8] = &quot;SEPTEMBER&quot;;
  15. montha[9] = &quot;OCTOBER&quot;;
  16. montha[10] = &quot;NOVEMBER&quot;;
  17. montha[11] = &quot;DECEMBER&quot;;
  18. var dailyhabitshubmonth = new Date();
  20. var getdhmonth = montha[dailyhabitshubmonth.getMonth()];
  21. Logger.log(getdhmonth);
  23. var mhs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(getdhmonth);
  24.   var monthhabitsogdata = mhs.getRange(&quot;C56:E70&quot;);
  25. var gethabits = monthhabitsogdata.getValues();
  26. Logger.log(gethabits);
  28. var dhs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&quot;DAILY HUB&quot;);
  29.   var habitsarea = dhs.getRange(&quot;K80:K94&quot;);
  30. monthhabitsogdata.copyTo(habitsarea);
  32. //THIS IS WHERE I AM HAVING TROUBLE
  33. var datavalues = dhs.getRange(&quot;K80:K94&quot;).getValues();
  34. var data_leng = datavalues.length;
  35. for(var i=0; i&lt;data_leng; i++) {
  36.   if(datavalues[i][0].length != 0) {
  37.     dhs.getRange(i+1,14).insertCheckboxes();
  38.   } 
  39. }
  41. }

<!-- end snippet -->

答案1

得分: 1

以下是翻译后的内容：

REPLACE

// 这是我遇到问题的地方
var datavalues = dhs.getRange("K80:K94").getValues();
var data_leng = datavalues.length;
for (var i = 0; i < data_leng; i++) {
if (datavalues[i][0].length != 0) {
dhs.getRange(i + 1, 14).insertCheckboxes();
}
}

WITH

var startRow = 80
var endRow = 94
var datavalues = dhs.getRange("K" + startRow + ":K" + endRow).getValues()
var data_leng = datavalues.length;
for (var i = 0; i < data_leng; i++) {
if (datavalues[i][0] !== "") {
dhs.getRange(i + startRow, 13).insertCheckboxes()
}
}

英文:

You want to insert a checkbox on Column M when there is a value in the same row of column K.

There are two problems with this part of your script:

  • evaluating whether the cell has a value
  • defining the target range for the checkbox

Does the cell have a value?

  • length returns the number of records in an array, but it is not a good method for determining whether a cell contains a value. This is a popular topic; you might care to read Google Spreadheets Scripts: check if cell is empty for several methods.
  • a better approach is !== &quot;&quot;

Defining the target cell

  • dhs.getRange(i+1,14).insertCheckboxes(); - there are two problems here
    • Column M is 13
    • i starts at zero, so the first range value would be .getRange(1,14) = Cell N1.
    • so you need a variable that defines the startRow, such as:
      • var startRow = 80

REPLACE

  1. //THIS IS WHERE I AM HAVING TROUBLE
  2. var datavalues = dhs.getRange(&quot;K80:K94&quot;).getValues();
  3. var data_leng = datavalues.length;
  4. for(var i=0; i&lt;data_leng; i++) {
  5.   if(datavalues[i][0].length != 0) {
  6.     dhs.getRange(i+1,14).insertCheckboxes();
  7.   } 
  8. }

WITH

  1.   var startRow = 80
  2.   var endRow = 94
  3.   var datavalues = dhs.getRange(&quot;K&quot;+startRow+&quot;:K&quot;+endRow).getValues()
  4.   var data_leng = datavalues.length;
  5.   for(var i=0; i&lt;data_leng; i++) {
  6.     if(datavalues[i][0] !==&quot;&quot;) {
  7.       dhs.getRange(i+startRow,13).insertCheckboxes()
  8.     } 
  9.   }

答案2

得分: 0

以下是代码部分的中文翻译：

  1. /** 建议
  2.  * 1. 遍历范围K80:K94中的值并识别非空值。
  3.  * 2. 获取每个非空值的行号。
  4.  * 3. 为了减少循环中的运行时间，如果有连续的非空值，将它们设置为一个范围（例如，M80:M81）。否则，单个值将被设置为单个范围（例如，M83）。
  5.  * 4. 遍历这些范围并插入复选框。
  6.  */
  7. var range = SpreadsheetApp.getActive().getRange('K80:K94');
  8. var temp_values = range.getValues().map((x, i) => x != '' ? [x, (range.getLastRow() - (range.getNumRows() - i) + 1)].flat() : '*');
  9. var ranges = temp_values.join().split('*').map(y => (y.replace(/[a-zA-Z,]+/g, '-')).split('-').filter(x => x != ''));
  10. ranges.map(z => [...new Set([z[0], z[z.length - 1]])]).forEach(
  11.   row => row.length > 1 ? SpreadsheetApp.getActive().getRange(`M${row[0]}:M${row[1]}`).insertCheckboxes() :
  12.     SpreadsheetApp.getActive().getRange(`M${row[0]}`).insertCheckboxes()
  13. );
  14. /** 结束 */

希望这对您有所帮助。

英文:

SUGGESTION

In my understanding, here's your goal:

  • Check values in K80:K94
  • Insert a checkbox on a row in M that is adjacent to a row that isn't empty in the K80:K94 range.

Perhaps you could try this sample script to replace your current line on the section in inserting the check-boxes:

  1.   /** SUGGESTION
  2.    *  1. Iterate through the values in range K80:K94 &amp; identify which aren&#39;t empty.
  3.    *  2. Get each non-empty values&#39; row numbers.
  4.    *  3. To reduce runtime execution in the loop, if there are consecutive non-empty values, set them as a range (e.g. M80:M81). Otherwise a single value will be set as a single range (e.g. M83);
  5.    *  4. Iterate through these ranges &amp; insert the checkboxes.
  6.    */
  7.   var range = SpreadsheetApp.getActive().getRange(&#39;K80:K94&#39;);
  8.   var temp_values = range.getValues().map((x, i) =&gt; x != &#39;&#39; ? [x, (range.getLastRow() - (range.getNumRows() - i) + 1)].flat() : &#39;*&#39;);
  9.   var ranges = temp_values.join().split(&#39;*&#39;).map(y =&gt; (y.replace(/[a-zA-Z,]+/g, &#39;-&#39;)).split(&#39;-&#39;).filter(x =&gt; x != &#39;&#39;));
  10.   ranges.map(z =&gt; [...new Set([z[0], z[z.length - 1]])]).forEach(
  11.     row =&gt; row.length &gt; 1 ? SpreadsheetApp.getActive().getRange(`M${row[0]}:M${row[1]}`).insertCheckboxes() :
  12.       SpreadsheetApp.getActive().getRange(`M${row[0]}`).insertCheckboxes()
  13.   );
  14.   /** End */

> This sample script runs faster vs your current implementation as it shortens the data to be processed in the loop

Demo

  • Sample sheet

如果列包含文本，则在列中添加复选框 Google Sheets 应用脚本。

  • After running the script

如果列包含文本，则在列中添加复选框 Google Sheets 应用脚本。

huangapple
  • 本文由 发表于 2023年2月18日 03:34:57
  • 转载请务必保留本文链接：https://go.coder-hub.com/75488565.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:

确定