Banks often want a CSV or NACHA file. This example exports a simple CSV of approved direct-deposit lines to Drive.
directDepositExport skips non-APPROVED rows, escapes CSV fields, and names files ACH_yyyyMMdd_HHmmss.csv.
FOLDER_ID must be a Drive folder the script user can write. Real banks may require stricter formats — treat this as a template.
Never log full account numbers to shared sheets without controls; protect PayExport.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet / Item | Field | Purpose |
|---|---|---|
| PayExport | A employeeId | Employee key |
| PayExport | B routing | ABA routing |
| PayExport | C account | Account number |
| PayExport | D amount | Net pay |
| PayExport | E status | APPROVED to include |
| FOLDER_ID | Drive folder | Export destination |
What this script does
Approved-row CSV export to Drive for bank upload.
Prerequisites
PayExport curated; Drive folder; banking format confirmed.
- Status APPROVED
- Protect PII
- Test with penny file
Walkthrough
Mark two rows APPROVED; run; open Drive CSV.
Edge cases
Throws if nothing approved — prevents empty bank files.
- CSV escaping for commas
- Timestamped filenames
Security
Limit sheet sharing, prefer Drive permissions over emailing account numbers, and delete old export files on a schedule.
How to test
Include a comma in employeeId to verify quotes.
Hardening for production
NACHA fixed-width generator; encrypt file; notify payroll Slack.
Variations
Email CSV as MailApp attachment to a secure inbox.
Full code: directDepositExport()
Set FOLDER_ID, approve rows, run directDepositExport(), then download the CSV from Drive.
/**
* Export approved direct-deposit rows to a bank CSV in Drive.
*/
const PAY = "PayExport"; // A employeeId, B routing, C account, D amount, E status
const FOLDER_ID = "YOUR_PAY_EXPORT_FOLDER";
function directDepositExport() {
const sheet = SpreadsheetApp.getActive().getSheetByName(PAY);
const values = sheet.getDataRange().getValues();
const lines = ["employeeId,routing,account,amount"];
for (let i = 1; i < values.length; i++) {
if (String(values[i][4]).toUpperCase() !== "APPROVED") continue;
const row = [values[i][0], values[i][1], values[i][2], Number(values[i][3]) || 0]
.map(function (v) {
const s = String(v);
return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
}).join(",");
lines.push(row);
}
if (lines.length === 1) throw new Error("No APPROVED rows to export");
const csv = lines.join("\n");
const name = "ACH_" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd_HHmmss") + ".csv";
const folder = DriveApp.getFolderById(FOLDER_ID);
const file = folder.createFile(name, csv, MimeType.CSV);
Logger.log("Wrote %s (%s bytes)", file.getUrl(), csv.length);
return file.getUrl();
}- Line 12: Inclusion filter for export rows.
- Line 23: Filename prefix with timestamp.
- Line 25: Writes CSV into the Drive folder.
- Line 20: Fails fast on empty exports.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: direct deposit export
- 1FOLDER_ID correct
- 2Only APPROVED rows intended
- 3Routing/account validated
- 4Bank format requirements reviewed
- 5PayExport access restricted
- 6Penny test with bank if new
Frequently asked questions
No — it is a simple CSV template. Many banks accept CSV; others require NACHA.
MailApp.sendEmail({attachments:[file.getBlob()], ...}) to a secure recipient.
Stamp exportedAt and skip already exported rows.
Change columns; keep CSV escaping.
Yes after approval cutoff — still review amounts first.
Store tokens elsewhere; export from a secured system when possible.
Sets the Drive file type for the created file.