Generate a PDF from a sheet row and file it in Drive

TL;DR — Turn a sheet row into a PDF (fill a template tab or Doc, export), save it into the right client folder in Drive with clear naming, choose overwrite vs dated copies, and optionally email it — plus a soft NitroGAS note for comments on the export and filing steps.

Ops wants a PDF of each approved row, dropped in that client’s Drive folder, named so a human can find it six months later. The spreadsheet already has the data. The missing piece is a boring, repeatable export → file → (maybe) email loop — not another “download as PDF” click every Friday.

TL;DR

Fill a template (hidden sheet tab or a Docs template) from the row, then export as PDF
Save into the client folder with a clear name (Client_Invoice_2026-10-29.pdf)
Decide up front: overwrite the same filename, or keep dated copies
Optional: attach the PDF to a short notice email
Soft CTA: Co-Pilot can leave plain-English comments on the export and filing steps for whoever maintains it next

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

The shape of the job

1. Read the active (or queued) row as an object
2. Fill a template tab / Doc with those values
3. Export that template as PDF bytes
4. Find (or create) the client’s Drive folder
5. Save with a stable name; overwrite or date-stamp
6. Optional: email the PDF, then clear the template for the next run

If you already batch “one PDF per client,” groupObjectsByKey turns the rows into { client → [rows…] } first so you open each folder once.

What not to do

Don’t email a PDF that never landed in Drive — file first, attach second
Don’t put client names only in the email subject; the filename and folder are what people search
Don’t leave the filled template tab visible and half-written if the export dies mid-run
Don’t hard-code folder ids for every client — a lookup sheet (Client → Folder id) scales

Fill a template tab, export as PDF

A dedicated “Print” tab (often hidden) is enough for many invoices and one-pagers. Write the row into named cells, export that sheet’s PDF URL, then wipe the tab.

/**
 * Fill the Print tab from a row object and return PDF bytes.
 * Template cells: B2=Client, B3=Amount, B4=Note (adjust to your layout).
 */
function rowToPdfBytes_(row) {
  var ss = SpreadsheetApp.getActive();
  var print = ss.getSheetByName('Print');
  print.getRange('B2').setValue(row.Client || '');
  print.getRange('B3').setValue(row.Amount || '');
  print.getRange('B4').setValue(row.Note || '');
  SpreadsheetApp.flush(); // make sure values land before export

  var url = 'https://docs.google.com/spreadsheets/d/' + ss.getId()
    + '/export?format=pdf'
    + '&gid=' + print.getSheetId()
    + '&portrait=true&fitw=true&sheetnames=false&printtitle=false'
    + '&pagenumbers=false&gridlines=false&fzr=false';

  var token = ScriptApp.getOAuthToken();
  var resp = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true
  });
  if (resp.getResponseCode() !== 200) {
    throw new Error('PDF export failed: HTTP ' + resp.getResponseCode());
  }
  return resp.getBlob().setName(buildPdfName_(row));
}

function buildPdfName_(row) {
  var day = Utilities.formatDate(new Date(), ssTimezone_(), 'yyyy-MM-dd');
  var client = String(row.Client || 'Unknown').replace(/[\\/:*?"<>|]/g, '-');
  return client + '_Invoice_' + day + '.pdf';
}

function ssTimezone_() {
  return SpreadsheetApp.getActive().getSpreadsheetTimeZone();
}

Docs templates work the same idea with DocumentApp + Drive.Files.export (Advanced Drive Service) or a Docs → PDF export URL. Keep one pattern per project so the next person isn’t learning both.

File it in the client folder

function filePdfForRow_(row, blob) {
  var folderId = lookupClientFolderId_(row.Client); // from a Config / Clients sheet
  var folder = DriveApp.getFolderById(folderId);
  var name = blob.getName();

  // Overwrite: remove prior files with the same name in this folder
  // Dated copies: skip this loop and always create — the date in the name keeps them unique
  var existing = folder.getFilesByName(name);
  while (existing.hasNext()) {
    existing.next().setTrashed(true);
  }
  return folder.createFile(blob);
}

function lookupClientFolderId_(client) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Clients');
  var values = sheet.getDataRange().getValues();
  var headers = values.shift();
  var iClient = headers.indexOf('Client');
  var iFolder = headers.indexOf('FolderId');
  for (var i = 0; i < values.length; i++) {
    if (String(values[i][iClient]) === String(client)) return values[i][iFolder];
  }
  throw new Error('No Drive folder mapped for client: ' + client);
}

Overwrite vs dated copies: overwrite when there’s one “current” invoice per client; dated copies when audit / history matters. Put the choice in a Script Property (pdfNaming = overwrite|dated) so you don’t argue in code review.

Optional: email the PDF

function emailPdf_(row, file) {
  var to = row.Email;
  if (!to) return;
  GmailApp.sendEmail(to, 'Your document — ' + row.Client, 'Attached.', {
    attachments: [file.getAs(MimeType.PDF)],
    name: 'Ops'
  });
}

Wire it behind a flag. Some clients want the Drive link only; some want the attachment; some want both.

One menu action for the active row

function exportActiveRowToPdf() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var row = readActiveRowAsObject_(sheet); // your header-map helper
  try {
    var blob = rowToPdfBytes_(row);
    var file = filePdfForRow_(row, blob);
    emailPdf_(row, file); // no-op if Email blank / flag off
    ss.toast('Filed: ' + file.getName(), 'PDF', 8);
  } finally {
    // Always wipe the template so the next preview never shows this client's data
    ss.getSheetByName('Print').getRange('B2:B4').clearContent();
  }
}

For many rows, group first:

var byClient = groupObjectsByKey(pendingRows, 'Client');
Object.keys(byClient).forEach(function (client) {
  byClient[client].forEach(function (row) { /* export + file */ });
});

Failure modes worth naming

Export URL 401/403 — the script needs Drive/Sheets scope and the user must have access to the file; run once from the editor to grant.
Missing client folder — fail loud with the client name; don’t dump orphans in My Drive.
Filename collisions across clients — include the client (or an id) in the name even inside a per-client folder.
Template left dirty — clear the Print tab in a finally so the next run doesn’t leak last client’s numbers into a preview.
Huge rows / images — PDF export time grows; run from a menu or a queue worker, not a hair-trigger onEdit.

Soft Co-Pilot note

NitroGAS keeps helpers like groupObjectsByKey in the catalog. Ask Co-Pilot to leave plain-English comments on the export URL, the folder lookup, and the overwrite-vs-dated choice so the next person isn’t decoding UrlFetch parameters at midnight. Free core; Co-Pilot is a pass when you want that pass of comments.

Closing checklist

Template tab / Doc fills from a header-mapped row object
PDF lands in the correct client folder with a searchable name
Overwrite vs dated copies is an explicit choice
Email is optional and behind a flag
Template cleared after export (success or failure)

Happy Coding!