Hello! I’m having more difficulty than I’d like to replace text with a newline.
In the Airtable Find & Replace extension script, I’ve modified it to support RegExp find and special character replace. However, when I replace the text with a special character like a newline, it always comes out in a literal text format.
Here’s a real basic example of what I’m doing:
let originalValue = record.getCellValue(field); let findText = "<br>"; let replaceText = "\n"; let newValue = originalValue.replace(findText, replaceText);
Every time I view the replaced results, there is always a \n in the output:
Is it possible to ensure that Airtable treats \n (I’m even doing \u{005C} to try to get around it and it doesn’t work) as an actual newline and not literally as “\n” ?
Page 1 / 1
Ok, I think I figured it out. Output value of input.textAsync() is escaped, e.g. if I put in \n then it will be represented as \\n
So now I have a map of special characters to convert from escaped versions.
Here’s the edited Find & Replace script with RegExp support and special character replacement if you’re interested:
let settings = input.config({ title: 'Find and replace', description: `This script will find and replace all text matches for a text-based field you pick. You will be able to see all matches before replacing them.`, items: [ input.config.table('table', { label: 'Table' }), input.config.field('field', { parentTable: 'table', label: 'Field' }), ], });
let { table, field } = settings; output.text(`Finding and replacing in the ${field.name} field of ${table.name}.`);
let findText = await input.textAsync('Enter text to find:'); let replaceText = await input.textAsync('Enter to replace matches with:'); let detectRegExp = await input.buttonsAsync('Detect RegExp?', ['Yes', 'No']);
if (detectRegExp === 'Yes') { const RE_TEXT_IS_RE = /^\/(.*)\/([dgimsuvy]*)$/; if (RE_TEXT_IS_RE.test(findText)) { const matches = RE_TEXT_IS_RE.exec(findText); if (matches) { findText = new RegExp(matches[1], matches[2]); output.markdown(`Using RegExp find: \`${findText.source}\``); } }
let shouldReplace = await input.buttonsAsync('Are you sure you want to save these changes?', [ { label: 'Save', variant: 'danger' }, { label: 'Cancel' }, ]);
if (shouldReplace === 'Save') { // Update the records let updates = replacements.map((replacement) => ({ id: replacement.record.id, fields: { [field.id]: replacement.after, }, }));
// Only up to 50 updates are allowed at one time, so do it in batches while (updates.length > 0) { await table.updateRecordsAsync(updates.slice(0, 50)); updates = updates.slice(50); } } }
I updated it slightly to put the “Detect RegExp” in the extension settings. You can also preview the special character replacement text in a way that is more visual:
let settings = input.config({ title: 'Find and replace', description: `This script will find and replace all text matches for a text-based field you pick. You will be able to see a preview of all matches before replacing them.`, items: [ input.config.table('table', { label: 'Table' }), input.config.field('field', { parentTable: 'table', label: 'Field' }), input.config.select('detectRegExp', { label: 'Detect RegExp and special character replacement', description: `When enabled it will detect if you've used RegExp literal notation within the search text field and turn it into a RegExp. Using RegExp means you can do case insensitive searches, as well as global search and replace operations, to name a few. You can also use special character replacement such as \\n for newlines and \\t for tabs.`, options: [ { label: 'No', value: 'n' }, { label: 'Yes', value: 'y' }, ], }), ], });
let { table, field, detectRegExp } = settings; output.text(`Finding and replacing in the ${field.name} field of ${table.name}.`);
const replacePreview = []; if (detectRegExp === 'y') { if (RE_TEXT_IS_RE.test(findText)) { const matches = RE_TEXT_IS_RE.exec(findText); if (matches) { findText = new RegExp(matches[1], matches[2]); output.markdown(`Detected RegExp: \`${findText.source}\` (\`${findText.flags}\`)`); } }
if (RE_ESCAPED_CHARS.test(replaceText)) { let replaceTextPreview = replaceText; for (const char in ESCAPED_CHARS) { const sc = ESCAPED_CHARS[char]; const re = new RegExp(sc.re, "g"); replaceText = replaceText.replace(re, char); replaceTextPreview = replaceTextPreview.replace(re, sc.preview); replacePreview.push([new RegExp(RegExp.escape(char), "g"), sc.preview]); } if (replaceText !== replaceTextPreview) { output.markdown(`Using special character replacement text: \`${replaceTextPreview}\``); } } }
// Load all of the records in the table let result = await table.selectRecordsAsync();
// Find every record we need to update let replacements = []; let newValues = []; for (let record of result.records) { let originalValue = record.getCellValue(field);
// Skip non-string records if (typeof originalValue !== 'string') { continue; }
// Skip records which don't have the value set, so the value is null if (!originalValue) { continue; }
let newValue = originalValue.replace(findText, replaceText); let newValuePreview = newValue; for (const x of replacePreview) { newValuePreview = newValuePreview.replace(x[0], x[1]); }
let shouldReplace = await input.buttonsAsync('Are you sure you want to save these changes?', [ { label: 'Save', variant: 'danger' }, { label: 'Cancel' }, ]);
if (shouldReplace === 'Save') { // Update the records let updates = replacements.map((replacement, index) => ({ id: replacement.record.id, fields: { [field.id]: newValues[index], }, }));
// Only up to 50 updates are allowed at one time, so do it in batches while (updates.length > 0) { await table.updateRecordsAsync(updates.slice(0, 50)); updates = updates.slice(50); }
