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