Help

Import a CSV file from an attachment field in an Airtable Automation

1249 1
cancel
Showing results for 
Search instead for 
Did you mean: 
nachumi_e
4 - Data Explorer
4 - Data Explorer

 

Hello, So Airtable has a script that allows importing a CSV file to update records, 
I am trying to use this script in an Automation but instead of uploading the CSV to the script, I need it to grab it from an attachment field. 

The Airtable Script: 

// Ask the user to import a CSV file containing a header row
let csvFileResult = await input.fileAsync(
    'Upload a CSV file',
    {allowedFileTypes: ['.csv'], hasHeaderRow: true}
);
// The file importer will automatically parse contents for many file types, including CSV files
let csvRows = csvFileResult.parsedContents;
// Edit this to the name of a table in your base
let table = base.getTable('Purchases');
let shouldContinue = await input.buttonsAsync(
    `Import ${csvRows.length} records from ${csvFileResult.file.name} into ${table.name}?`,
    [{label: 'Yes', variant: 'primary'}, 'No']
)
if (shouldContinue === 'Yes') {
    // Create new records from the CSV.
    // Edit these field and property names to match your table and CSV data
    let newRecords = csvRows.map(csvRow => ({
        fields: {
            'Date': csvRow.Date,
            'Amount': csvRow.Amount
        }
    }));
    // A maximum of 50 record creations are allowed at one time, so do it in batches
    while (newRecords.length > 0) {
        await table.createRecordsAsync(newRecords.slice(0, 50));
        newRecords = newRecords.slice(50);
    }
}

So I have asked my best friend 😁 (GPT) to write me a new script that might be able to do that, Tho it is not working and i wanted to see if anyone can help me modify the script to achieve what i am looking for.

The custom script:

// Replace these values with your own
const apiKey = 'your-api-key';
const baseId = 'your-base-id';
const tableName = 'Table';
const attachmentFieldName = 'Attachment Field';

async function importCSVFromAttachmentField(recordId) {
  // Initialize the Airtable client
  const airtable = new Airtable({ apiKey }).base(baseId);

  try {
    // Retrieve the record with the attachment field
    const record = await airtable(tableName).find(recordId);

    // Check if the attachment field has a value
    if (!record.get(attachmentFieldName)) {
      console.log('Attachment field is empty');
      return;
    }

    // Get the URL of the attachment file
    const attachmentUrl = record.get(attachmentFieldName)[0].url;

    // Download the CSV file
    const response = await fetch(attachmentUrl);
    const csvText = await response.text();

    // Parse the CSV text into an array of objects
    const lines = csvText.split('\n');
    const headers = lines[0].split(',');
    const records = lines.slice(1).map((line) => {
      const values = line.split(',');
      return headers.reduce((record, header, i) => {
        record[header] = values[i];
        return record;
      }, {});
    });

    // Insert the records into the base
    await airtable(tableName).create(records);
    console.log(`Imported ${records.length} records`);
  } catch (error) {
    console.error(error);
  }
}

// Run the function when the button is clicked
input.button.onClick(() => {
  importCSVFromAttachmentField(input.recordId);
});

nachumi_e_0-1673314504666.png

 

1 Reply 1
BradleyGraber
4 - Data Explorer
4 - Data Explorer

The only trouble I had with this script was I was using the wrong URL for the attachment.  There is a viewer URL, and an "Expiring Download" URL  I used the airtable input GUI to select the Expiring Download URL, and it worked.  I'm not sure how to select it using the recordId and scripting.