CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
Ian Propst-Campbell
Ian Propst-Campbell

Reputation: 158

Google Sheets: Comparing Columns with Checkboxes

I am trying to create a to do list spreadsheet. In one column, "Daily Tasks", I have a list of tasks. In the other column, I have checkboxes. My goal is to create a script that will add all of the "checked" tasks to an array.

I am attempting to do this in my script using a nested for loop. However, when I log my new array that should contain the checked items (trueArr), I see just a series of what appear to be empty arrays.

How do I change my script so that my array contains the checked items from my spreadsheet?

Here is a link to my spreadsheet

Here is my code:

//spreadsheet 
var ss = SpreadsheetApp.getActiveSpreadsheet();

//worksheet 
var todoList = ss.getSheetByName("To-Do List");


function completedArray(){
var tasks = todoList.getRange("c6:d22").getValues();
var checks = todoList.getRange("b6:b22").getValues();

var trueArr = [];
  
for(var i =0; i <tasks.length; i++){
  for(var j=0; j<checks.length;j++){
    if(checks[j]===true){
      trueArr.push(tasks[j]) 
            }  
        }
    Logger.log(trueArr);
    }
}

In my log, I expect to see the same items that are on my "Daily Tasks" list on my spreadsheet, but instead I only see empty arrays.

Upvotes: 1

Views: 124

Answers (2)

Ian Propst-Campbell
Ian Propst-Campbell

Reputation: 158

I found a way to create the same array of checked items using the filter method and an arrow function. Made a two dimensional array that includes both checkboxes and list items. Then I filtered by the checkboxes in the first column. No need for loops!

//spreadsheet 
var ss = SpreadsheetApp.getActiveSpreadsheet();

//worksheet 
var todoList = ss.getSheetByName("To-Do List");
var dailies = todoList.getRange("b6:e22").getValues();



function checkedItems(){

var checkedItems = dailies.filter(row => row[0] === true); 

Logger.log(checkedItems);

}

Got the same result with a fraction of the code!

Upvotes: 0

Marios
Marios

Reputation: 27380

Explanation / Issues:

You are very close besides the following two things:

  • The checks array is a 2 dimensional array since you use getValues() to create it. Meaning that checks[j] is a row or in other words a 1 dimensional array. It is fundamentally wrong to compare an array with a value. Instead you should be using checks[j][0] in the if statement since this will iterate over each value of the single column. Another approach would be to use flat to convert the 2D array to 1D and then use your current code.

  • The second issue has to do with a for loop that you don't use anywhere. Your code iterates over i but you don't use or need i in your code. Keep also in mind that tasks and checks have the same number of rows (same length), therefore one for loop is enough in this scenario.

Solution:

//spreadsheet 
var ss = SpreadsheetApp.getActiveSpreadsheet();

//worksheet 
var todoList = ss.getSheetByName("To-Do List");


function completedArray(){
var tasks = todoList.getRange("c6:d22").getValues();
var checks = todoList.getRange("b6:b22").getValues();

var trueArr = [];
  

  for(var j=0; j<checks.length;j++){
    if(checks[j][0]===true){
      trueArr.push(tasks[j]) 
            } 
    }
  Logger.log(trueArr);
}

The structure of trueArr would be: [[task2, task2], [task3, task3], [task9, task9]] since tasks[j] is also a row or 1D array. This is why you will end up with a collection of 1D arrays.

Upvotes: 2

Related Questions