google 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	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

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]);

}

Date	Payee	Category	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;
    }
  }
}

Share Improve this question edited Mar 19 at 19:58 asked Mar 19 at 17:21 Craig 235 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 0

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

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

google apps script - Find first empty row and IGNORE the empty checkboxes - Multiple Sheets - Stack Overflow

1 Answer 1

Using break instead of return

与本文相关的文章

评论列表(0)