Export CRM Notes to CSV Without Breaking Commas or Line Breaks
How can I export CRM notes from Google Sheets to a UTF-8 CSV file without breaking commas, quotes, or line breaks?
Use a bound Google Apps Script to quote displayed values, double embedded quotes, and separate records with CRLF. Save the UTF-8 CSV to Drive, then import it with a parser that supports quoted multiline fields.
To export CRM notes from Google Sheets, put the displayed cell values into quoted CSV fields, then save the UTF-8 file to Drive. This preserves commas, quotation marks, and line breaks as long as the receiving importer supports quoted multiline fields.
How does the exporter preserve CRM note boundaries?
The exporter puts quotes around every field, doubles any quotation marks inside a field, and separates records with CRLF. Line breaks within a note stay inside that field. RFC 4180 describes this structure: fields containing commas, quotes, or line breaks need surrounding quotes, and a quote within a field is represented by two quotes.
Between records, the script writes \r\n. A line break inside a note remains part of the field, not a record separator. A CSV parser that handles quoted multiline fields can read the note as one field. A program that splits the file at every physical newline can still split the note.
The exporter reads the text shown in the sheet, not formulas or formatting. According to the Apps Script Range reference, getDisplayValues() returns displayed cell values as strings. That means a displayed date or a visible leading zero is exported as text. The destination may still infer a different type during import, so set identifier columns to Text when the exact characters must survive.
What sample CRM data should the source tab contain?
Set up a tab named CRM Notes, put headers in row 1, and add a few notes with commas, quotes, and line breaks. The entries below are examples, not contact records to upload.
| Lead ID | Contact | Notes |
|---|---|---|
| CRM-001 | Pixel Placeholder | Call Tuesday, after 2 PM. |
| CRM-002 | Nova Example | Said “email only”. |
| CRM-003 | Orbit Demo | Requested recap.[LF]Send Monday. |
The contacts above are fictional placeholders. Replace [LF] with an actual line break in the cell so that Send Monday. appears on the second line.
The script reads the rectangle from A1 through the last row and column containing content. Blank cells and blank rows inside that rectangle stay in the export. Hidden and filtered-out rows are not removed. Keep unrelated information off the source tab, and check the exported file before sending it to another system.
If the sheet is also your working contact list, remove columns you do not want exported and clean up the data first. The guide to cleaning a contact spreadsheet for a HubSpot CSV import covers common cleanup decisions. For help choosing small-business tools, see the small business stack topic page.
What Apps Script code creates the quoted UTF-8 CSV?
A bound Apps Script reads the displayed values from the chosen tab, formats each row as CSV, and creates a timestamped file in the Drive folder you specify. It quotes every field, including values that would not need quotes based on their contents.
function exportCrmNotesCsv() {
const properties = PropertiesService.getScriptProperties();
const sheetName =
properties.getProperty('SOURCE_SHEET_NAME') || 'CRM Notes';
const folderId = properties.getProperty('EXPORT_FOLDER_ID');
if (!folderId || !folderId.trim()) {
throw new Error(
'Set EXPORT_FOLDER_ID in Script Properties to a Drive folder ID.'
);
}
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error(
'Run this script from an Apps Script project bound to a spreadsheet.'
);
}
const sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
throw new Error('Sheet not found: ' + sheetName);
}
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();
if (lastRow === 0 || lastColumn === 0) {
throw new Error('The source sheet is empty: ' + sheetName);
}
const values = sheet
.getRange(1, 1, lastRow, lastColumn)
.getDisplayValues();
const csv =
values
.map(function (row) {
return row.map(quoteCsvField).join(',');
})
.join('\r\n') + '\r\n';
const timestamp = Utilities.formatDate(
new Date(),
'Etc/UTC',
"yyyyMMdd'T'HHmmssSSS'Z'"
);
const fileName = 'crm-notes-' + timestamp + '.csv';
const blob = Utilities.newBlob('')
.setDataFromString(csv, 'UTF-8')
.setContentType('text/csv')
.setName(fileName);
const folder = DriveApp.getFolderById(folderId.trim());
const file = folder.createFile(blob);
Logger.log('CSV saved: ' + file.getUrl());
return file.getUrl();
}
function quoteCsvField(value) {
return '"' + String(value).replace(/"/g, '""') + '"';
}
quoteCsvField converts the input to text, changes each quotation mark to two quotation marks, and wraps the field in quotes. Backslash escapes such as \" are not CSV escapes in this script. The final + '\r\n' ends the last record. A conforming parser does not treat that ending as an extra data record.
Because the script calls getDisplayValues(), the export contains visible text rather than the underlying raw value. For CSV field and record structure, follow RFC 4180. No API key is needed. Google authorization provides access to the spreadsheet and Drive actions.
How do you configure the source tab and Drive destination?
Bind the script to the source spreadsheet, set a Drive folder ID in Script Properties, and choose a folder where the account running the script can create files. Leave only the columns you want to export on the source tab.
- Open the source spreadsheet. Add headers in row 1, and move unrelated data off the source tab.
- Create the bound project. Choose Extensions, then Apps Script. Replace the starter code with the full script above and save.
- Choose a Drive folder. Pick a folder where the account running the script can create files. Check its sharing settings before exporting sensitive notes.
- Copy the folder ID. In the folder’s browser address, copy the part after
/folders/. Do not use the folder name or the full address. - Set Script Properties. In Apps Script Project Settings, add
EXPORT_FOLDER_ID. AddSOURCE_SHEET_NAMEusing the tab’s exact name, including capitalization and spaces. Without that property, the code usesCRM Notes. - Authorize and export. Select
exportCrmNotesCsv, click Run, and review the permissions request. Authorize the intended account. If authorization stops the run, run the function again. - Locate the CSV. Find the saved file’s Drive address in the execution log. Download the file if the importer requires a local upload.
Each run creates a new timestamped file; it does not replace an earlier export. The script neither changes sharing permissions nor sends the file to another service. Before making exports routine, decide who owns the destination folder and how long old files should be kept.
What CSV output should the sample CRM notes produce?
The sample creates four CSV records, counting the header. Each record has three fields, and the final note contains a line break within its field. The output below shows how quotation marks inside a quoted field are doubled.
"Lead ID","Contact","Notes"
"CRM-001","Pixel Placeholder","Call Tuesday, after 2 PM."
"CRM-002","Nova Example","Said ""email only""."
"CRM-003","Orbit Demo","Requested recap.
Send Monday."
Markdown does not reliably show whether line endings are LF or CRLF. In the saved file, every record ends with CRLF, including the last one. The line break inside the note remains LF.
The final data record has this escaped representation:
"CRM-003","Orbit Demo","Requested recap.\nSend Monday."\r\n
Here, \n and \r\n stand for control characters, not literal backslashes. Counting physical lines in a text viewer will not show the number of CSV records. One multiline note can take up several physical lines and still be a single CSV field.
In Excel, choose Data, then From Text/CSV. Select UTF-8 and a comma delimiter, then choose Transform Data. If a multiline note splits across rows, open the Source step under Applied Steps and set line break handling to ignore quoted line breaks. The formula bar displays this as QuoteStyle.Csv. Set identifier columns to Text if automatic conversion could remove leading zeros. Quotes mark field boundaries; they do not force the importer to store a field as text.
How do you fix missing folders, missing tabs, and split multiline notes?
Check folder and tab settings to resolve configuration or permission errors. When a multiline note splits into separate rows, use an importer that understands CSV. Read the exact error message before changing the quoting logic.
Missing destination folder
Symptom: Set EXPORT_FOLDER_ID in Script Properties to a Drive folder ID.
Exact fix: In this project’s Project Settings, add EXPORT_FOLDER_ID. Enter the folder ID, not a blank value or the full address, save the setting, and run exportCrmNotesCsv again.
If the error names DriveApp.getFolderById or folder.createFile, confirm that the ID points to a folder and that the executing account can create files there. If that account lacks access, use a folder it controls.
Missing source tab
Symptom: Sheet not found: followed by a name.
Exact fix: Set SOURCE_SHEET_NAME to the exact label on the bottom tab, not the spreadsheet document title. Remove spaces at the start or end unless they are part of the tab name.
Missing spreadsheet or empty source
Exact fix: To resolve the bound-project error, open the source spreadsheet, choose Extensions, then Apps Script, and add the code there. For The source sheet is empty:, add data to the configured tab or choose the correct populated tab.
Multiline note becomes another row
Symptom: Send Monday. appears as a separate record, or the column counts do not match.
Exact fix: Set the importer to read quoted multiline fields, use " as the quote character, and treat doubled quotes as embedded quotes. Import the original Drive file instead of copied preview text.
If the importer handles each physical line separately, switch to a CSV-aware parser. Do not split the file at newlines or remove quotation marks. When the destination alters an identifier such as CRM-001 or a numeric ID with leading zeros, set that destination column to text during import. Quotes show where fields begin and end; they do not prevent spreadsheet software from treating a note as a formula. For untrusted notes, import them as text or use a destination API with typed text fields.
When should you avoid this displayed-value CSV exporter?
Use another method if you need raw typed values, the file is too large for the script’s in-memory approach, or spreadsheet software must not interpret untrusted notes as formulas. Choose based on the receiving system’s requirements, not just its ability to accept a .csv file.
- Large archives: The script keeps the full CSV in memory. If execution or memory limits become a problem, use a native CRM export or a streaming job with a CSV library. Appending arbitrary chunks can damage record boundaries or encoding.
- Machine-readable values: Use a dedicated export tab with explicit formats, or serialize individual fields from raw values. Replacing
getDisplayValues()withgetValues()alone will not fix the issue: values such as date objects need deliberate conversion. - Untrusted notes: Quoting does not prevent spreadsheet formula injection. A note that begins with
=may become a formula. Import notes as text. If the exact content must stay unchanged, use an API with typed text fields rather than adding prefixes.
Displayed values work well when a person will review the export, but they may not suit system-to-system exchange. If the destination expects identifiers, timestamps, or other typed fields, define those rules before exporting. For another spreadsheet cleanup task before CRM import, see normalizing CRM email addresses before a CSV import.