Overview
This is a helper function for jpfieber’s Paycheck Deduction Transaction Generator.
It will automatically (with a click) insert the generated transaction rows into the Transactions sheet.
Installation
It is a Google Apps Script function. Copy and paste the code into any *.gs file within ‘Apps Script’ from the Extension menu.
Setup
You need the Paycheck Deduction Transaction Generator installed, and make note of whatever you named that sheet/tab. I have a his and hers version installed.
There is a check right at the beginning of the code to make sure we are on one of the Paycheck sheets/tabs, so you should replace with whatever name(s) you use.
There are a couple of other things to check that I commented, like where to do the inserting on the Transactions sheet, because yours may be set up differently.
Default is to insert between rows 2 and 3, with data starting in column B.
Usage
Call the function paycheckDeductionTransactionAutoInsert from a custom UI menu, or a button click:
You can create a button in Google Sheets to trigger a script:
- Insert a drawing or an image in the sheet (Insert > Drawing or Insert > Image).
- Right-click the object and select Assign script.
- Enter the name of your function (e.g.,
paycheckDeductionTransactionAutoInsert).
Permissions
Is it ok for others to copy, use, and modify your workflow?
Go nuts!
The Code
Code is here on GitHub.
Also a static version:
function paycheckDeductionTransactionAutoInsert() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const currentSheet = ss.getActiveSheet();
const sheetName = currentSheet.getName();
// Check if the sheet name is one of my "Paycheck Deduction Transaction Generator" sheets.
if (sheetName !== "Paycheck-Adam" && sheetName !== "Paycheck-Eve") {
SpreadsheetApp.getUi().alert(
`Error: This script can only be run on Paychecks sheets.`
);
return; // Exit the script without doing anything
}
const transactionsSheet = ss.getSheetByName("Transactions");
if (!transactionsSheet) {
SpreadsheetApp.getUi().alert("Error: Sheet 'Transactions' does not exist.");
return;
}
// Dynamically concatenate values from AQ10 and AQ12 to form the range address
// These are the two ranges in the "Select $AT$4 Through $BO$16" section.
const rangeStart = currentSheet.getRange("AQ10").getValue();
const rangeEnd = currentSheet.getRange("AQ12").getValue();
// Make a range with full colon
const rangeAddress = `${rangeStart}:${rangeEnd}`;
if (!rangeStart || !rangeEnd) {
SpreadsheetApp.getUi().alert("Error: Cells AQ10 and AQ12 must not be empty.");
return;
}
// Get the number of rows to insert from AQ5
// This is the number in the "Insert nn Rows in Transactions Sheet" section.
const rowsToInsert = currentSheet.getRange("AQ5").getValue();
if (!Number.isInteger(rowsToInsert) || rowsToInsert < 0) {
SpreadsheetApp.getUi().alert("Error: Cell AQ5 must contain a non-negative integer.");
return;
}
try {
// Insert rows between row 2 and 3 in the Transactions sheet
// I have had issues with conditional formatting or arrayformulas if I try to insert at the very top.
// Feel free to do this differently if you like to live dangerously.
if (rowsToInsert > 0) {
transactionsSheet.insertRowsAfter(2, rowsToInsert);
}
// Get the data from the specified concatenated range
const range = currentSheet.getRange(rangeAddress);
const data = range.getValues();
// Paste the data into the Transactions sheet starting at B3
// Your Transactions sheet may be different, but I use the first column for indicators, hence column B
transactionsSheet
.getRange(3, 2, data.length, data[0].length) // Row 3 and Column 2 (B3)
.setValues(data);
} catch (e) {
SpreadsheetApp.getUi().alert(`Error: ${e.message}`);
}
}
Enjoy!