Your Vendor Charges for an Integration. You Already Have the Export.

Read part one: Gmail to Sheets

If I am already paying for the software, I get a little cranky when moving my own data comes with another subscription.

In the first article, I showed how to take a CSV report emailed by a vendor and automatically refresh a Google Sheet. That gets rid of the daily download-and-copy routine. But if my team works in Salesforce, a spreadsheet is only halfway there.

So here is the next step: take the rows from that spreadsheet and create or update Salesforce records. The vendor sends the export. Google handles the handoff. Salesforce gets the data.

Yes, this step uses an API. The distinction is whose API: we are using Salesforce access you already have, rather than paying the source vendor to unlock theirs. For readers following along, confirm Salesforce API access is enabled in your own org.

This takes an admin to set up, and someone still owns it afterward. But for a small daily update, I would rather spend that time once than approve a recurring connector bill without checking my options.

What we are connecting

Vendor CSV → Google Sheet → Salesforce records.

This follows the first article's complete-snapshot import. We will match each row using a stable source record ID. Salesforce calls this an upsert: update the record if the ID already exists, or create it if it does not.^[1]

Our example is a simple vendor-record list. Each row has Record ID, Name, Status, and Amount. Use a sandbox and test data first. A new snapshot updates the mapped records; it does not delete Salesforce records that disappear from the export.

1. Give Salesforce a reliable match

Ask your Salesforce admin to create a test object called Vendor Record, API name Vendor_Record__c. Use a Text record Name. Add these custom fields:

The source ID is the important part. It must stay the same for the same record, across every export. A row number does not count. Neither does a company name that someone can edit. If several vendors can use the same ID, add a stable vendor prefix to the exported ID before importing it.

The script rejects blank IDs and repeated IDs within a snapshot. Mark the Salesforce field Unique as well as External ID so Salesforce enforces the match too.

2. Create the Salesforce connection

In Salesforce Setup, search for External Client App Manager. Create a local External Client App for this integration, enable OAuth, and select the Manage user data via APIs (api) scope. Configure the client credentials flow and its execution user using Salesforce's current setup guide.^[2]

Use a dedicated integration user with API permission and only the object and field access needed for this import. For the example, that means Read, Create, and Edit on Vendor Record and the mapped fields. Your admin should apply your organization's app-access policies.

For new connections, use an External Client App. Salesforce restricts creation of new Connected Apps as of Spring '26; existing Connected Apps can still work.^[3]

Open the app's Settings tab to find Consumer Key and Secret. The key becomes your client ID, and the secret becomes your client secret. Keep both in the approved integration configuration.

3. Put the settings in Script Properties

Open the Apps Script project from the CSV article. Go to Project Settings → Script Properties. Keep its existing spreadsheet and CSV settings. Add the Salesforce settings below and save. Values do not need wrapping quotation marks.

{"Name":"Name","Status":"Status__c","Amount":"Amount__c"}

The left side is the CSV column heading, exactly as it appears in the sheet. The right side is the Salesforce field's API name. Record ID is handled separately by the source-ID settings; do not put the external ID field in the mapping again.

For the example, Amount should be an unformatted decimal such as 1250.50, without a currency symbol or thousands separator. The numeric-field setting converts it from the text imported by article one. This starter version supports text fields and explicitly configured numbers; dates, checkboxes, relationships, and other types need additional conversion rules.

Script Properties keep the settings out of the code and sheet cells. They are not a separate secrets vault: project editors can access them. Restrict access to the script and its bound spreadsheet to trusted editors.^[4]

4. Add the script

Add a new file in the same project called Salesforce.gs. Paste this code and save. Keep importLatestCsv from article one. You do not need the earlier email-log version of the Salesforce script.

This example handles up to 200 rows in one snapshot. It stops if the report is larger, instead of quietly sending only the first part. Salesforce's collection endpoint supports up to 200 records per request.^[1] A larger report needs batching and checkpoints.

// Add to the project from the CSV-import article. Configure Script Properties.
function syncCsvToSalesforce() {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);
  try {
    const props = PropertiesService.getScriptProperties();
    const p = props.getProperties();
    for (const name of ['SPREADSHEET_ID','SF_DOMAIN','SF_CLIENT_ID',
      'SF_CLIENT_SECRET','SF_API_VERSION','SF_OBJECT','SF_EXTERNAL_ID_FIELD',
      'SF_SOURCE_ID_COLUMN','SF_FIELD_MAP']) {
      if (!p[name] || !p[name].trim()) throw new Error('Missing Script Property: ' + name);
    }
    const domain = p.SF_DOMAIN.replace(/\/$/, '');
    if (!/^https:\/\/[a-z0-9.-]+\.my\.salesforce\.com$/i.test(domain)) {
      throw new Error('SF_DOMAIN must be your Salesforce My Domain HTTPS URL.');
    }
    if (!/^v\d+\.\d+$/.test(p.SF_API_VERSION)) throw new Error('Use an API version such as v65.0.');
    const validApiName = name => /^[A-Za-z][A-Za-z0-9_]*$/.test(name);
    if (!validApiName(p.SF_OBJECT) || !validApiName(p.SF_EXTERNAL_ID_FIELD) ||
      p.SF_EXTERNAL_ID_FIELD === 'Id') throw new Error('Check the object and external ID API names.');
    let map;
    try { map = JSON.parse(p.SF_FIELD_MAP); } catch(e) { throw new Error('SF_FIELD_MAP must be valid JSON.'); }
    if (!map || typeof map !== 'object' || Array.isArray(map) || !Object.keys(map).length) {
      throw new Error('SF_FIELD_MAP must map CSV column names to Salesforce field API names.');
    }
    const mappedFields = Object.values(map);
    if (mappedFields.some(field => typeof field !== 'string' || !validApiName(field) ||
      ['Id','attributes',p.SF_EXTERNAL_ID_FIELD].includes(field)) ||
      new Set(mappedFields).size !== mappedFields.length) throw new Error('Check the field mapping: duplicate or reserved target.');
    const numericFields = new Set((p.SF_NUMBER_FIELDS || '').split(',').map(s=>s.trim()).filter(Boolean));
    if ([...numericFields].some(field=>!mappedFields.includes(field))) throw new Error('SF_NUMBER_FIELDS must use mapped Salesforce API names.');
    const ss = SpreadsheetApp.openById(p.SPREADSHEET_ID.trim());
    const sheetName = p.SHEET_NAME || 'Vendor Import';
    if (sheetName === 'Salesforce Sync Log') throw new Error('Use a different source tab name.');
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet || sheet.getLastRow() < 2) throw new Error('No CSV data available to sync.');
    const values = sheet.getDataRange().getValues();
    const headers = values.shift().map(String);
    if (new Set(headers).size !== headers.length) throw new Error('CSV headers must be unique.');
    const sourceIndex = headers.indexOf(p.SF_SOURCE_ID_COLUMN);
    if (sourceIndex < 0 || Object.keys(map).some(name=>!headers.includes(name))) {
      throw new Error('Source ID column or mapped CSV column is missing.');
    }
    const rows = values.map((row,i)=>({row,sheetRow:i+2})).filter(x=>x.row.some(v=>v !== ''));
    if (!rows.length || rows.length > 200) throw new Error('This example supports 1 to 200 data rows per snapshot.');
    const seen = new Set();
    const records = rows.map(({row,sheetRow})=>{
      const id = String(row[sourceIndex]).trim();
      if (!id || id.length > 255 || seen.has(id.toLowerCase())) {
        throw new Error('Missing, long, or duplicate source ID at sheet row ' + sheetRow);
      }
      seen.add(id.toLowerCase());
      const record = {attributes:{type:p.SF_OBJECT},[p.SF_EXTERNAL_ID_FIELD]:id};
      for (const [header,field] of Object.entries(map)) {
        const value = row[headers.indexOf(header)];
        if (value === '') continue; // Blank source values do not clear Salesforce fields.
        if (numericFields.has(field)) {
          const text = String(value).trim();
          if (!/^-?\d+(\.\d+)?$/.test(text) || !Number.isFinite(Number(text))) {
            throw new Error('Use an unformatted decimal for ' + header + ' at sheet row ' + sheetRow);
          }
          record[field] = Number(text);
        } else { record[field] = String(value); }
      }
      return record;
    });
    const signature = Utilities.base64EncodeWebSafe(Utilities.computeDigest(
      Utilities.DigestAlgorithm.SHA_256,
      JSON.stringify([domain,p.SF_API_VERSION,p.SF_OBJECT,p.SF_EXTERNAL_ID_FIELD,records])
    ));
    if (props.getProperty('LAST_SF_SIGNATURE') === signature) {
      console.log('Mapped snapshot unchanged since the last successful sync.'); return;
    }
    const tokenResponse = UrlFetchApp.fetch(domain + '/services/oauth2/token', {
      method:'post',followRedirects:false,muteHttpExceptions:true,
      payload:{grant_type:'client_credentials',client_id:p.SF_CLIENT_ID,client_secret:p.SF_CLIENT_SECRET}
    });
    if (tokenResponse.getResponseCode() !== 200) {
      throw new Error('Salesforce authentication failed: HTTP ' + tokenResponse.getResponseCode());
    }
    const token = JSON.parse(tokenResponse.getContentText());
    if (!token.access_token || !/^https:\/\/[a-z0-9.-]+\.my\.salesforce\.com$/i.test(token.instance_url)) {
      throw new Error('Unexpected Salesforce token response.');
    }
    const response = UrlFetchApp.fetch(token.instance_url + '/services/data/' +
      p.SF_API_VERSION + '/composite/sobjects/' + p.SF_OBJECT + '/' + p.SF_EXTERNAL_ID_FIELD, {
      method:'patch',contentType:'application/json',followRedirects:false,muteHttpExceptions:true,
      headers:{Authorization:'Bearer ' + token.access_token},
      payload:JSON.stringify({allOrNone:false,records})
    });
    if (response.getResponseCode() !== 200) throw new Error('Salesforce upsert failed: HTTP ' + response.getResponseCode());
    const results = JSON.parse(response.getContentText());
    if (!Array.isArray(results) || results.length !== records.length) throw new Error('Unexpected upsert result count.');
    const logRows = results.map((result,i)=>[
      rows[i].sheetRow,result.success ? 'Synced' : 'Failed',
      result.success ? (result.created ? 'Created' : 'Updated') :
        (result.errors || []).map(e=>e.statusCode || 'UNKNOWN_ERROR').join(', ')
    ]);
    const log = ss.getSheetByName('Salesforce Sync Log') || ss.insertSheet('Salesforce Sync Log');
    log.clearContents();
    log.getRange(1,1,logRows.length+1,3).setValues([
      ['Source sheet row','Status','Result'],...logRows
    ]);
    log.setFrozenRows(1);
    SpreadsheetApp.flush();
    if (results.some(result=>!result.success)) throw new Error('Some Salesforce rows failed. Check Salesforce Sync Log.');
    props.setProperties({LAST_SF_SIGNATURE:signature,LAST_SF_SUCCESS_AT:new Date().toISOString()});
    console.log('Synced ' + records.length + ' CSV records to Salesforce.');
  } finally { lock.releaseLock(); }
}

function importCsvAndSyncSalesforce() {
  importLatestCsv();
  syncCsvToSalesforce();
}

5. Test the match, not just the connection

1. Import a small CSV with two harmless sample records into a test spreadsheet using article one.
2. Check that SF_DOMAIN and the app credentials point to your sandbox.
3. Run syncCsvToSalesforce. Review Google's authorization prompt for external requests.
4. Open Salesforce and confirm two Vendor Records were created with the right IDs and values.
5. Change a sample Amount in the test sheet and run again. The existing record should update; the record count should stay at two.
6. Run again without changes. The script should skip the unchanged mapped snapshot.
7. Check the Salesforce Sync Log tab for row results.

An overall HTTP success does not mean every row succeeded. The script checks the individual results and leaves the snapshot eligible for retry if any row fails. Fix the failed row or Salesforce rule, then rerun. Previously successful rows may be updated again during that retry.

Blank source values are omitted, so they do not clear existing Salesforce values. Updates replace the mapped nonblank fields, so use fields your import is allowed to own. Test validation rules and flows that run on the target object; a repeated update can trigger automation again.

6. Schedule the full handoff

Once the sandbox checks pass and your admin has approved the intended destination, configure that destination and replace the first article's trigger with one for importCsvAndSyncSalesforce. Choose Time-driven → Hour timer → Every hour and enable failure notifications. Remove the old import-only trigger so you have one scheduled wrapper.

The wrapper imports the latest CSV first, then syncs its mapped rows. If the CSV import fails, the Salesforce step does not run. If the CSV is already imported but Salesforce failed previously, the Salesforce step can still retry.

Keep Vendor Import for the raw CSV and Salesforce Sync Log for the script's latest row results. The log is replaced each time a Salesforce batch is processed, so put your own notes elsewhere.

A few things this workaround does not magically solve

It will not remove records that disappear from an export, automatically reconcile Salesforce edits, or make yesterday's file fresh. Keep the delivery checks from article one. LAST_SF_SUCCESS_AT records a completed Salesforce sync, not proof that the vendor sent a new report.

The script skips an unchanged mapped snapshot using LAST_SF_SIGNATURE. If someone changes or deletes a destination record, that alone will not trigger a resync. Delete that tracking property deliberately if you need to resend the current snapshot. A changed snapshot resends all its rows, so plan for any automation triggered by updates.

For authentication or permission errors, check the app's execution user, OAuth policy, domain, and field access. For failed rows, start with the error codes in Salesforce Sync Log. If API limits, retries, or volume become a regular issue, it is time to build a more robust integration or buy a supported one.

Make the bill earn its place

There is a point where a supported connector is worth the price. I just want to reach that decision after looking at what the tools we already pay for can do.

For a small scheduled update, the export may be enough. Put the settings somewhere your team can maintain, test the record matching, and keep the recurring bill off the stack until it earns its place.

References and screenshot credits

1. Salesforce: Upsert records using sObject Collections
2. Salesforce: External Client App client credentials setup
3. Salesforce: REST API authorization
4. Google: Script Properties
5. Salesforce Trailhead: External Client App setup and credentials-location screenshots. Images are referenced from Salesforce's image host and require an internet connection.

Read part one: Gmail to Sheets

Want a second set of eyes on your field mapping or duplicate rules? Bring your flow to a RevOps Therapy session. Explore RevOps Therapy