google-sheetsgoogle-apps-scriptcheckbox

Find first empty row and IGNORE the empty checkboxes


I am trying to write a script to automatically input a row of data based on data found in another sheet (I have this and it seems to work):

function Paycheck() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const MB = ss.getSheetByName("Monthly Budget");
    const Utilities = ss.getSheetByName("Utilities");
    const data = MB.getDataRange().getValues();
    const out = [data[4][12], data[4][13], data[4][14], data[4][15], data[4][16], data[4][17], data[4][18]];
    Utilities.getRange(Utilities.getLastRow()+1, 1, 1, out.length).setValues([out]);

}

HOWEVER, on the target sheet "Utilities" (shown in a Markdown tablet below), there are checkboxes in column E that I would like to keep. However, if present, the row identified as the last available row is the next row after the checkboxes (for now I have deleted the checkboxes from rows 12 onward).

Is there a way to get the script to identify the next available row while ignoring the checkbox? (e.g. paste in row 6?) Row 6 is the first empty row WITH a checkbox in column E. Row 12 is the first empty row WITHOUT a checkbox in column E that the script is currently identifying.

Date Payee Category Memo Tax Related Debit Credit Balance
1/1/2025 Starting Balance FALSE $218.52
1/5/2025 Test City Utilities: Water FALSE $112.78 $105.74
1/6/2025 Dominion Energy Utilities: Gas FALSE $99.03 $6.71
1/9/2025 Utilities Deposit Salary FALSE $229.50 $236.21
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
3/16/2025 Utilties Deposit Salary $229.50 $465.71

Solution

  • To check every row, use the .every() method to differentiate whether it is blank or has value. I changed the naming convention by using lowercase letters at the beginning instead of capital letters to avoid conflicts with the Utilities function in Google Apps Script.

    Modified Script:

    function Paycheck() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const mb = ss.getSheetByName("Monthly Budget");
      const utilities = ss.getSheetByName("Utilities");
      const data = mb.getDataRange().getValues();
      const out = [data[4][12], data[4][13], data[4][14], data[4][15], data[4][16], data[4][17], data[4][18]];
    
      // gets all the data in the Utilities sheet.
      const utilData = utilities.getDataRange().getValues();
      
      for (let i = 2; i < utilData.length; i++) {
        // checks each cell if it has a value; then it will return true; otherwise, it will return false if the row has a value and the condition will not trigger.
        const position = [0, 1, 2, 3, 5, 6, 7].every(x => utilData[i][x] === '');
        if (position && (utilData[i][4] === false || utilData[i][4] === '')) {
          utilities.getRange(i + 1, 1, 1, out.length).setValues([out]);
          return;
        }
      }
    }
    
    

    This will insert the new data into row 6 instead of row 12

    Reference: