google-sheetsgoogle-apps-scriptcheckbox

How can I add an unchecked checkbox to sorted data in Google Sheets?

I have a Google Form that takes entered information and outputs it to a Google Sheet. The responses from this form are then sorted in a separate tab. I want this information to be sorted by most recent entry timestamp, and automatically add an unchecked checkbox to each new entry in the sorted tab. The checkbox will then be ticked by another user when the task is complete.

Example:

My existing sorted data looks like this:

| Column A  | Column B | Column C | Column D | 
|-----------|----------|----------|----------|
|    ▣      | OldEntry2| Value 2  | Result 2 |
|    ▣      | OldEntry1| Value 1  | Result 1 |

When new entries are received, the desired result is for the data to be sorted by Column B, and an unchecked checkbox added in Column A:

| Column A  | Column B | Column C | Column D | 
|-----------|----------|----------|----------|
|    □      | NewEntry | Value 3  | Result 3 |
|    ▣      | OldEntry2| Value 2  | Result 2 |
|    ▣      | OldEntry1| Value 1  | Result 1 |

However, due to row misalignment after sorting, the checkbox column is not sorted correctly:

| Column A  | Column B | Column C | Column D | 
|-----------|----------|----------|----------|
|    ▣      | NewEntry | Value 3  | Result 3 |
|    ▣      | OldEntry2| Value 2  | Result 2 |
|    □      | OldEntry1| Value 1  | Result 1 |

I was previously using the =sort() function to sort the data, and data validation + conditional formatting to add the checkboxes in the sorted tab, but when new entries were received via the form, the checkboxes are not sorted correctly. I have learned that adding additional information to a sorted sheet results in row misalignment, and I will need a script to perform this action.

I have found a similar question & solution here on stackoverflow, but after several failed attempts I was unable to apply this script properly to my own sheet.

I edited the script in the above example to fit my spreadsheet, and this is the version of the script I am currently using:

function updateSorted() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Import_range");
  var sortedSh = ss.getSheetByName("Sorted");

  /*
  Check if the Sorted Sheet has data. 
  If true, compare Sorted Sheet and Import_range data
  else, copy Import_range data to Sorted Sheet. 
  */
  if(sortedSh.getLastRow() > 1){ 
    
    var sData = sortedSh.getRange(2, 1, sortedSh.getLastRow()-1, 10).getValues(); //remove unnecessary data
    var sDataFiltered = sData.filter(e => e[2]); //get Date column of Sorted sheet
    var sDates = sortedSh.getRange(2, 10, sortedSh.getLastRow()-1, 1).getValues().flat(); 
    var sDatesFiltered = sDates.filter((a) => a); //remove unnecessary data
    
    
    var importDates = sh.getRange(2, 1, sh.getLastRow()-1, 1).getValues().flat(); //get Date column of Import_range
    
    //convert dates to String format
    var arr1 = sDatesFiltered.map( dateString => String(dateString))
    var arr2 = importDates.map( dateString => String(dateString))
  
    var difference = arr2.filter(x => !arr1.includes(x)); //get the difference between arr1 and arr2
    difference.forEach(dates => {
      
      var index = arr2.indexOf(dates); //get new entry position in Import_range
      var newEntry = sh.getRange(index+2, 1, 1, 9).getValues().flat(); //get row data of new entry from Import_range
      newEntry.unshift(false); //append false in the beggining of the array
      sDataFiltered.push(newEntry); //append new entry to sDataFiltered array
    })

    var range = sortedSh.getRange(2, 1, sDataFiltered.length, 10);
    range.setValues(sDataFiltered); //set the values of Sorted sheet using the sDataFiltered array as data
    sortedSh.getRange(2, 1, sDataFiltered.length, 1).insertCheckboxes(); //get the checkbox range and insert checkbox
    range.sort({column: 2, ascending: false}); //sort the data by Date column

  }else{
    var range = sh.getRange(2, 1, sh.getLastRow()-1, 4);
    var data = range.getValues();
    data.forEach(row => {
      row.unshift(false)
    })
    var sortedRange = sortedSh.getRange(2, 1, data.length, 10);
    sortedRange.setValues(data);
    sortedSh.getRange(2, 1, data.length, 1).insertCheckboxes();
    sortedRange.sort({column: 2, ascending: false});
  }
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Update Sorted Sheet', 'updateSorted')
      .addToUi();
}

This script is successfully adding new entries to my sorted tab, and also adding unchecked boxes to the new entries. However, it is resulting in duplicate entries in my sorted tab, similar to below:

| Column A  | Column B | Column C | Column D | 
|-----------|----------|----------|----------|
|    □      | NewEntry | Value 3  | Result 3 |
|    □      | NewEntry | Value 3  | Result 3 |
|    ▣      | OldEntry2| Value 2  | Result 2 |
|    □      | OldEntry2| Value 2  | Result 2 |
|    ▣      | OldEntry1| Value 1  | Result 1 |
|    □      | OldEntry1| Value 1  | Result 1 |

What corrections do I need to make in my script to get it to function properly without generating duplicate entries?

Here are the links to my Example Sheet and the accompanying Entry Form.

Solution

  • You may try:

    const updateSorted = () => {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var fr = ss.getSheetByName("Form Responses 1");
  var vl = fr.getRange(2, 1, fr.getLastRow() - 1, 9).getValues().pop();
  ss.getSheetByName("Sorted").insertRowBefore(3).getRange(3, 1, 1, vl.length).setValues([vl]).offset(0, vl.length, 1, 1).insertCheckboxes();
}

    This gets what's in the last row of the Form Responses 1 sheet and adds a new row in the Sorted sheet, shifting its current data down. Afterwards, the values are added in the inserted row with a checkbox. When paired with an installable form submit trigger, the script displays the data as if it were sorted by the most recent entry timestamp and avoids any row misalignment issue.

    REFERENCES