google-sheetsgoogle-apps-script

Find first empty row and IGNORE the empty checkboxes - Multiple Sheets


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

@Lime Husky was able to help me with this, but I need additional help modifying the script to accomodate MULTIPLE sheets.

The modified script seems to perform the first sheet, but does not complete the Mortgage sheet:

function Paycheck() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const MB = ss.getSheetByName("Monthly Budget");
const utilities = ss.getSheetByName("Utilities");
const Mortgage = ss.getSheetByName("Mortgage");
const data = MB.getDataRange().getValues();
var out = [data[4][12], data[4][13], data[4][14], data[4][15], data[4][16], data[4][17], data[4][18]];
var utilData = utilities.getDataRange().getValues();
for (let i = 2; i < utilData.length; i++) {
    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;
    }
  }
var out = [data[5][12], data[5][13], data[5][14], data[5][15], data[5][16], data[5][17], data[5][18]];
var MortgageData = Mortgage.getDataRange().getValues();
for (let i = 2; i < MortgageData.length; i++) {
    const position = [0, 1, 2, 3, 5, 6, 7].every(x => MortgageData[i][x] === '');
    if (position && (MortgageData[i][4] === false || MortgageData[i][4] === '')) {
      Mortgage.getRange(i + 1, 1, 1, out.length).setValues([out]);
      return;
    }
  }
}

Solution

  • Using break instead of return

    The break statement terminates the closest enclosing iteration statement (that is, for , foreach , while , or do loop) or switch statement.

    When a return statement is encountered inside a loop, it not only ends the loop but also exits the entire function.

    Modified Script:

    function Paycheck() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const MB = ss.getSheetByName("Monthly Budget");
      const utilities = ss.getSheetByName("Utilities");
      const mortage = ss.getSheetByName("Mortgage");
      const data = MB.getDataRange().getValues();
      let out = [data[4][12], data[4][13], data[4][14], data[4][15], data[4][16], data[4][17], data[4][18]];
    
      function insertData(range, output) {
        const rangeValues = range.getDataRange().getValues();
        for (let i = 2; i < rangeValues.length; i++) {
          const position = [0, 1, 2, 3, 5, 6, 7].every(x => rangeValues[i][x] === '');
          if (position && (rangeValues[i][4] === false || rangeValues[i][4] === '')) {
            range.getRange(i + 1, 1, 1, output.length).setValues([output]);
            break;
          }
        }
        return
      }
    
      insertData(utilities, out);
      out = [data[5][12], data[5][13], data[5][14], data[5][15], data[5][16], data[5][17], data[5][18]];
      insertData(mortage, out);
    }
    

    Reference

    Break in Javascript