Scripting: Convert Attached .TXT file to Long Text

Hi,
There is a fragment of code, where dtext is value of attachment field, loop inside a cell with multiple files in a single record

let upd=[]; 
for (let textdoc of dtext){
    let responce=await remoteFetchAsync(textdoc.url)
    let answer=await responce.text();
    upd.push({'id':existing.get(textdoc.name)||'','fields':{'TextField':answer}})
}

But in your case, with one file per field, I would process whole table query, skipping the empty cells
Something like this (you must adjust tab/fld names)
 

const table=base.getTable('YOUR_TABLE')
const query=await table.selectRecordsAsync({fields:['files']})
const file=(rec,txt=rec.getCellValue('files'))=>txt? txt[0]:null
const updates=[]
for (let rec of query.records) {
    if(!file(rec)) continue;
    let responce=await remoteFetchAsync(file(rec).url)
    let content=await responce.text()
    updates.push({id:rec.id,fields:{'TextField':content}})
}
console.log(updates)
while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

IMPORTANT NOTE: you should consider Long Text field limit: 100 Kb.

Thank you.  Most of the files are small.  I tried this code, with the following changes

Cartographers - Table name

TXT - attachment field

CARTOTXT - Longtext destination field

const table=base.getTable('Cartographers')
const query=await table.selectRecordsAsync({fields:['TXT']})
const file=(rec,txt=rec.getCellValue('TXT'))=>txt? txt[0]:null
const updates=[]
for (let rec of query.records) {
if(!file(rec)) continue;
let responce=await remoteFetchAsync(file(rec).url)
let content=await responce.text()
updates.push({id:rec.id,fields:{'CARTOTXT':content}})
}
console.log(updates)
while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

I must be missing something because it produced the following error

ReferenceError: remoteFetchAsync is not defined
at main on line 7

Kevin

That means you are running the code in automation script. I suppose you are going to do it in Scripting Extension. In order to fix it to run in automation, you should replace remoteFetchAsync with fetch.
But usually automation means different scenario, automation triggered by some record and you don’t need to query the whole table

 

There are several ways to do it, i did it by automation “When record matches condition” and small script
 

Important to select correct data for URL (not sure if it’s the most correct, but it’s working)
 

Actually, at first I added record id to query, but now realize it’s not needed. url variable is all data we need.

The reason why I added Count field - I don’t know what’s your trigger. If you trigger by ‘files’ update, it might not work, because after uploading, file needs a second or two to be processed and appear as thumbnail. If automation runs at that moment, it might not be able to read file properties, like url.
Formula field ‘Count’ changes from 0 to 1 (and more) right after file was processed and formula read it’s url.

Thats Count formula
 

(LEN(files)-LEN(SUBSTITUTE(files,'https://','')))/LEN('https://')

It’s just a way to make a little delay after file upload. Maybe you don’t need it.


So, finally, script outputs ‘content’ variable, and I used it in ‘Update record’ step.

Of course, you shoud maybe add something like “What if there 2 files” or “What if Files field empty .. etc...”

But I hope this info will help you to reach your goal. 

Thank you for looking into this. I’m confused.  The trigger is

WHEN RECORD MATCHES CONDITIONS: if TXT is not empty

the text files are already loaded into AirTable as attachments.

When we run this, we are getting an error

Error: Exceeded quota of 50 fetch requests per script invocation.
at main on line 7

We want the script to run whenever we add a new TXT file. We add 5-10 a day.

It seems like you mixed two possible ways of use,
for “the text files are already loaded” and for “to run whenever we add a new TXT file”


First scenario: to process files that are alredy in base, don’t use Automation.

Use Script Extension instead. To prevent overlimit 50 fetches per script, try to filter view to include <50 files. For example,. if you have a table with 200 files, add autonumber field and filter by it with “>150”. Also filter by ‘TextField’ is empty . Then, after first run, change first filter to ‘>100” and so on, to prevent view from having 50 or more files when you Run the script.

Insert the first code listing, with remoteFetchAsync.Change it to query the current view, not table.
here is a version to process view. Remember that files >100 KB cannot be processed.

const table=base.getTable('YOUR_TABLE')
const view=table.getView('YOUR_VIEW')
const query=await table.selectRecordsAsync({fields:['files']})
const file=(rec,txt=rec.getCellValue('files'))=>txt? txt[0]:null
const updates=[]
for (let rec of query.records) {
    if(!file(rec)) continue;
    let responce=await remoteFetchAsync(file(rec).url)
    let content=await responce.text()
    updates.push({id:rec.id,fields:{'TextField':content}})
}
console.log(updates)
while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

===============================================================================

Second scenario: you want to add 5-10 files and process them by automation

Use automation as I shown in previous comments, with 3 lines code only (i removed id)

const {url}=input.config()
const responce=await fetch(url[0])
output.set('content', await responce.text())

Note: you can use Automation for a few files that already in base. Just select a cell with file, press ‘Del’ to clean the cell, then Ctrl+Z to return file. Automation will be triggered as if you just added this file.

Alas, with 3000+ entries, we can’t reasonably manually process them in clusters of 50 at a time. I wonder if there is a workaround.

Also, the script as is still generating this error

Error

Error: Exceeded quota of 50 fetch requests per script invocation.

at main on line 8

It was running in a View with only 49 records.

const table=base.getTable('Cartographers')
const view=table.getView('TXTCONVERT')
const query=await table.selectRecordsAsync({fields:['TXT']})
const file=(rec,txt=rec.getCellValue('TXT'))=>txt? txt[0]:null
const updates=[]
for (let rec of query.records) {
    if(!file(rec)) continue;
    let responce=await fetch(file(rec).url)
    let content=await responce.text()
    updates.push({id:rec.id,fields:{'CARTOTXT':content}})
}
console.log(updates)
while(updates.length) await table.updateRecordsAsync(updates.splice(0,50))

Cartographer - Table

TXTONVERT - view

TXT - attachment field

CARTOTXT - target field.

Kevin

I am running it through an automation because I have no idea how to create a script extension.  I tried to follow some instructions online, but the AirTable documentation for novices just links to dead URLS with no instructions whatsoever.

It throws up the same error if there is only 1 view in the table view.

Kevin

sorry, my fault. it still querying whole table, 
instead of view
Here

const query=await table.selectRecordsAsync({fields:['files']})

must be
 

const query=await view.selectRecordsAsync({fields:['files']})

.
How to add extension:
 

Then, select “Start from scratch”
=============================================

Anyway, to run it for 3k records, I would use other way, with automation. There are a plenty of ways to do it, but it needs quite a long time to explain.
Like adding autonumber field and play with DATEADD, DATETIME_DIFF,
or link to other table and use lookups and their sort/limit number ability.
 

I'm a little late to this party, but if you want to use an automation, I suggest using a simpler script that acts on only one record. This way you do not need to manually run the script as new records are added, and you don't have to worry about 50 fetch request limit (unless you have over 50 attachments in a single record). You can loop through the records using a repeating action group, and place the script inside the repeating action group.