Before You Pay for an Integration, Try the CSV Export

Continue to part two: Sheets to Salesforce

One workaround I use pretty often in RevOps is a regular export. I have the data in one system, need it in another, and would prefer not to buy another subscription just to move it.

Integration fees can get ridiculous. And yes, someone will inevitably say, “Just use the API.” Great. If API access is included in your plan, you have someone who can build it, and that person has time to maintain it.

If the API is behind an upgrade and all I need is yesterday's report in a spreadsheet, I am going to look at the export button first.

When a vendor can email a CSV on a schedule, Google Apps Script can take that attachment and refresh a Google Sheet for you. No downloading the file every morning. No copying rows between tabs. No separate connector subscription for this step.

Here is how to set it up, with the settings kept outside the code so you can change the sender, filename, or destination without becoming the person who accidentally deleted a bracket.

What you are building

Scheduled vendor report → CSV attachment in Gmail → refreshed Google Sheet.

This version uses the newest matching attachment to replace the data in a dedicated import tab. It is designed for a complete report, such as the current open-opportunity list or a daily workload snapshot. Receiving the same file again will not stack another copy underneath it.

If each file contains only new transactions, you need an append-and-deduplicate version instead. Replacing the tab with an incremental export would remove earlier transactions. Decide which kind of report you have before you start.

First, check whether the workaround saves you money

Look for a recurring report that already supports emailed CSV attachments on your current vendor plan. Confirm that the delivery schedule is frequent enough for the decisions you make from it.

A download button alone does not make the process automatic. If the vendor only offers manual downloads, someone still has to retrieve the file. If the email contains a login-protected link rather than an attachment, this script will not download it.

For a simple comparison, a connector priced at $100 a month costs $1,200 a year. That is a hypothetical example, not a quoted vendor price. Subtract your setup and maintenance time to work out whether the workaround is worth it. Your existing Google account or Workspace costs and Apps Script limits still apply.

I would use this for a daily report before I used it for something that needs immediate, guaranteed updates. Saving money is useful. Quietly making your reporting unreliable is expensive.

1. Schedule a predictable CSV export

In your source system, set the report to email a CSV attachment to your Gmail account. Choose a fixed filename if the vendor allows it, such as daily-pipeline.csv.

For this example, use a comma-separated, UTF-8 CSV with one header row and the same columns each time. Keep the column order consistent. Excel workbooks, ZIP files, and semicolon-delimited exports need a different parser or an adjustment.

Send yourself a sample first. Check that it contains the full report, not a preview or a list of changes since the last export.

2. Create the destination spreadsheet

Create a Google Sheet and copy its ID: the part of the address between /d/ and /edit.

https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit

The script will create a tab called Vendor Import. Keep that tab for imported data only: its contents are replaced when a newer report arrives. Put formulas, charts, and notes on separate tabs that reference it. Keep access appropriate for the vendor data being imported.

3. Paste the script once

In your spreadsheet, choose Extensions → Apps Script. Replace the starter code with the script below and save the project as Emailed CSV Import.

You do not need to edit any values inside this code. You will add your settings in the next step.

// Paste once. Configure the workflow in Project Settings > Script Properties.
function importLatestCsv() {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);
  try {
    const props = PropertiesService.getScriptProperties();
    const p = props.getProperties();
    for (const name of ['SPREADSHEET_ID','SENDER_EMAIL','ATTACHMENT_NAME']) {
      if (!p[name] || !p[name].trim()) throw new Error('Missing Script Property: ' + name);
    }
    const days = positiveInteger_(p.LOOKBACK_DAYS || '7', 'LOOKBACK_DAYS', 365);
    const cap = positiveInteger_(p.MAX_THREADS || '100', 'MAX_THREADS', 500);
    const tabName = (p.SHEET_NAME || 'Vendor Import').trim();
    const sender = p.SENDER_EMAIL.trim().toLowerCase();
    if (!/^[a-z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-z0-9.-]+\.[a-z]{2,}$/i.test(sender)) {
      throw new Error('SENDER_EMAIL must be one email address.');
    }
    const cutoff = Date.now() - days * 86400000;
    const query = `from:${sender} has:attachment newer_than:${days}d`;
    const threads = GmailApp.search(query, 0, cap);
    if (threads.length === cap) {
      throw new Error('Search cap reached. Narrow LOOKBACK_DAYS or raise MAX_THREADS (maximum 500).');
    }
    let latest = null;
    for (const thread of threads) {
      for (const message of thread.getMessages()) {
        const date = message.getDate().getTime();
        const from = message.getFrom();
        const address = (from.match(/<([^>]+)>/) || [null,from])[1].trim().toLowerCase();
        if (message.isDraft() || date < cutoff || address !== sender) continue;
        if (p.SUBJECT_CONTAINS && !message.getSubject().toLowerCase()
          .includes(p.SUBJECT_CONTAINS.toLowerCase())) continue;
        const files = message.getAttachments({includeInlineImages:false})
          .filter(file => file.getName() === p.ATTACHMENT_NAME.trim());
        if (!files.length) continue;
        if (!latest || date > latest.date) {
          latest = {message,files,date};
        }
      }
    }
    if (!latest) throw new Error('No matching attachment found. Check the sender, filename, subject, and lookback.');
    if (latest.files.length !== 1) throw new Error('Newest email has multiple attachments with the configured filename.');
    const file = latest.files[0];
    const key = [p.SPREADSHEET_ID.trim(),tabName,latest.message.getId(),file.getHash()].join('|');
    if (props.getProperty('LAST_IMPORT_KEY') === key) {
      console.log('Already imported the latest matching CSV.'); return;
    }
    if (file.getSize() > 5 * 1024 * 1024) throw new Error('CSV exceeds this example\'s 5 MB limit.');
    const text = file.getDataAsString('UTF-8').replace(/^\uFEFF/, '');
    const rows = Utilities.parseCsv(text);
    while (rows.length && rows[rows.length-1].every(cell=>cell === '')) rows.pop();
    if (!rows.length || rows[0].every(cell=>cell.trim() === '')) throw new Error('CSV is empty or has no header.');
    const width = rows[0].length;
    if (rows.some(row=>row.length !== width)) throw new Error('CSV rows have inconsistent column counts.');
    if (rows.length * width > 100000) throw new Error('CSV exceeds this example\'s 100,000-cell limit.');
    const safeRows = rows.map(row=>row.map(value=>
      /^[\s]*[=+@-]/.test(value) ? "'" + value : value));
    const ss = SpreadsheetApp.openById(p.SPREADSHEET_ID.trim());
    const sheet = ss.getSheetByName(tabName) || ss.insertSheet(tabName);
    const oldRows = sheet.getLastRow(), oldCols = sheet.getLastColumn();
    if (oldRows && (oldCols !== width ||
      sheet.getRange(1,1,1,width).getDisplayValues()[0].join('\u001f') !== rows[0].join('\u001f'))) {
      throw new Error('CSV headers changed. Review the export layout before replacing the import tab.');
    }
    if (rows.length > sheet.getMaxRows()) {
      sheet.insertRowsAfter(sheet.getMaxRows(),rows.length-sheet.getMaxRows());
    }
    if (width > sheet.getMaxColumns()) {
      sheet.insertColumnsAfter(sheet.getMaxColumns(),width-sheet.getMaxColumns());
    }
    const range = sheet.getRange(1,1,rows.length,width);
    range.setNumberFormat('@');
    range.setValues(safeRows);
    if (oldRows > rows.length) {
      sheet.getRange(rows.length+1,1,oldRows-rows.length,width).clearContent();
    }
    sheet.setFrozenRows(1);
    SpreadsheetApp.flush();
    props.setProperties({
      LAST_IMPORT_KEY:key,
      LAST_SUCCESS_AT:new Date().toISOString(),
      LAST_FILE_DATE:new Date(latest.date).toISOString()
    });
    console.log(`Imported ${rows.length-1} data rows from the latest matching CSV.`);
  } finally { lock.releaseLock(); }
}

function positiveInteger_(value,name,max) {
  const number = Number(value);
  if (!Number.isInteger(number) || number < 1 || number > max) {
    throw new Error(name + ' must be a whole number from 1 to ' + max + '.');
  }
  return number;
}

4. Add your settings in Script Properties

Click the Project Settings gear in the left sidebar. Under Script Properties, select Add script property. Enter the property names and values below, then click Save script properties. Do not add quotation marks around the values.^[1]

If the vendor adds a date to every filename, ask whether you can use a fixed attachment name. This script deliberately matches one exact name so it does not accidentally import a different report from the same sender.

Later, change these properties when you need a different destination or report. Leave the script alone. Script Properties are shared within the project, so keep editor access limited to people who should control this import.

5. Run a test and check the actual data

1. Make sure a sample CSV email has arrived within your lookback window.
2. Select importLatestCsv in the editor's function dropdown, then click Run.
3. Review Google's authorization request for the account receiving the report. GmailApp requires broad Gmail permission even though this code only reads messages and attachments. Use your organization's approval process if needed.
4. Open Vendor Import in your spreadsheet. Compare its headers, row count, a few record IDs, and an important total with the source CSV.
5. Run the function again. The execution log should say that the latest CSV was already imported.

The script reads attachments with GmailApp and parses CSV using Google's Utilities.parseCsv, which handles quoted CSV fields.^[2] It checks the sender and subject on each message because Gmail search returns conversations.

Values are imported as text to preserve IDs and leading zeros. Formula-like values are escaped. Convert the numeric and date fields you need on your reporting tab; do not assume every column will arrive as a usable number or date.

After a successful run, the script adds LAST_SUCCESS_AT, LAST_FILE_DATE, and LAST_IMPORT_KEY to Script Properties. The first two tell you when the import ran and when the source email arrived. Leave those tracking properties alone unless you are deliberately resetting the import.

6. Put it on a schedule

1. In Apps Script, open Triggers using the clock icon.
2. Click Add Trigger.
3. Choose importLatestCsv and deployment Head.
4. Choose Time-driven → Hour timer → Every hour.
5. Enable failure notifications and save.

An hourly check can pick up the report after it arrives without you timing a manual download. The spreadsheet does not have to stay open. These triggers run as the account that creates them, so use an account your team can maintain.^[3]

This version keeps only the newest matching report. If several arrive between runs, it skips the older snapshots. It also stops when its search cap is reached rather than claiming it checked the whole mailbox.

Keep the savings from turning into cleanup

Set a simple freshness check: if a daily report should arrive by 8 a.m., someone needs to notice when yesterday's data is still there at 10. Failure notifications catch errors, but they do not catch every late report. A freshness alert is a useful next addition.

Apps Script has execution and service quotas, and sheet writes are not a database transaction.^[4] This is a small reporting workflow. Keep the source exports and check the output before using it for decisions that cannot wait.

When I would pay for the integration

If I need immediate updates, changes flowing both directions, large volumes, or a supported service with someone accountable when it breaks, I would price the proper integration. I would also include my team's maintenance time in that comparison.

But if the requirement is “put the daily report in this spreadsheet,” I am checking the CSV export before I approve another recurring bill.

References

1. Google: Script Properties and configuration
2. Google: GmailMessage attachments and Utilities.parseCsv
3. Google: Installable triggers
4. Google: Apps Script quotas

Continue to part two: Sheets to Salesforce