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

Question

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.

function dailyhubhabits() {

  

var montha = new Array(12);
montha[0] = "JANUARY";
montha[1] = "FEBRUARY";
montha[2] = "MARCH";
montha[3] = "APRIL";
montha[4] = "MAY";
montha[5] = "JUNE";
montha[6] = "JULY";
montha[7] = "AUGUST";
montha[8] = "SEPTEMBER";
montha[9] = "OCTOBER";
montha[10] = "NOVEMBER";
montha[11] = "DECEMBER";
var dailyhabitshubmonth = new Date();

var getdhmonth = montha[dailyhabitshubmonth.getMonth()];
Logger.log(getdhmonth);

var mhs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(getdhmonth);
  var monthhabitsogdata = mhs.getRange("C56:E70");
var gethabits = monthhabitsogdata.getValues();
Logger.log(gethabits);

var dhs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DAILY HUB");
  var habitsarea = dhs.getRange("K80:K94");
monthhabitsogdata.copyTo(habitsarea);

//THIS IS WHERE I AM HAVING TROUBLE
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();
  } 
}

}

Answer 1

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 !== ""

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

//THIS IS WHERE I AM HAVING TROUBLE
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()
    } 
  }

Answer 2

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:

  /** SUGGESTION
   *  1. Iterate through the values in range K80:K94 & identify which aren't empty.
   *  2. Get each non-empty values' row numbers.
   *  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);
   *  4. Iterate through these ranges & insert the checkboxes.
   */
  var range = SpreadsheetApp.getActive().getRange('K80:K94');
  var temp_values = range.getValues().map((x, i) => x != '' ? [x, (range.getLastRow() - (range.getNumRows() - i) + 1)].flat() : '*');
  var ranges = temp_values.join().split('*').map(y => (y.replace(/[a-zA-Z,]+/g, '-')).split('-').filter(x => x != ''));
  ranges.map(z => [...new Set([z[0], z[z.length - 1]])]).forEach(
    row => row.length > 1 ? SpreadsheetApp.getActive().getRange(`M${row[0]}:M${row[1]}`).insertCheckboxes() :
      SpreadsheetApp.getActive().getRange(`M${row[0]}`).insertCheckboxes()
  );
  /** End */

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

Demo

• Sample sheet

• After running the script