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;
}
}
}
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