How to use scripting app template "spreadsheet importer" to parse ebay reports into table?

[Fae]

I am a complete noob and know next to nothing about coding, so bear with me please.

I download order reports on ebay and upload them into my airtable base for a partner to gather the data from. They are downloaded automatically in .csv format, with no option to omit any data beforehand. The automatic csv import tool that airtable provides doesn’t work for these because they contain 63 columns. I only use 29 columns, and I reorganize them a little as well. I am trying to find a way to make this automatic.

I thought I would try and use the scripting extension to do this. I chose the example “spreadsheet importer” to use as a template. My knowledge of scripting is about 1 html website I made in grade 7 computer class 13 years ago above the average persons level of knowledge, aka slim to none. Here is how far I got before getting confused:

// 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(‘Critical Info’);

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: {
        'Order Number': csvRow.[OrderNumber],
        'Buyer': csvRow.BuyerUsername,
        'Item Number': csvRow.ItemNumber,
        'Item Title': csvRow.ItemTitle,
    }
}));

// 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);
}

}

This got me nowhere, I decided to run it to test before continuing to add fields and none of them worked. What am I doing wrong? Am I even on the right track at all?

[Andy_Cloke]

Hey, my answer was helpful on Reddit so thought I’d repeat it here:

Using download CSV URLs from ebay, you could make this even simpler by importing the remote CSVs directly from ebay with the Data Fetcher extension: How to Import a Remote CSV File in Airtable

No need for code!

No need for code!

[Fae]

Hi, thanks for responding in both cases. Unfortunately, I’m realizing that I can’t use data fletcher since I don’t know how to generate urls without even more complicated coding using ebay’s APIs. There’s no way to get a url simply, unless you know of a service where I can upload a csv file and have it generate a url. I did try using make/integromat, but it’s above my head.

Also, the csv files contain sensitive customer information so it needs to be secure.

[Fae]

okay, so going back to my original solution of the scripting app, I decided to test this part:

// Edit these field and property names to match your table and CSV data
let newRecords = csvRows.map(csvRow => ({
    fields: {
        'Order Number': csvRow.[OrderNumber],
        'Buyer': csvRow.BuyerUsername,
        'Item Number': csvRow.ItemNumber,
        'Item Title': csvRow.ItemTitle,
    }

By adding a row with a single word name that I knew matched the csv file column name. So on the bottom I added:

'Quantity': csvRow.Quantity,

And it worked! So where am I going wrong for the rest of it? Is the left or right side of the equation the destination field? How do I label two word fields?

[Andy_Cloke]

Hi Fae,

No problem at all! I’ll look into building an integration with ebay.

Until then, have you considered just importing all 63 fields with Airtable’s CSV import app, then hiding the ones you don’t want? That sounds the easiest manual option.

re your code, I think this maybe be an error: csvRow.[OrderNumber].

It should probably be csvRow.OrderNumber (without the []).

[Fae]

Thanks for the continued responses Andy,

I’m hoping to find an automatic solution as it can be very repetitive to do manually, I have between 30-50 orders per day on multiple accounts and I’m very busy with other aspects of the business as well.

What I’m doing right now is manually removing the fields I don’t need before they import (takes the same amount of time as hiding them would). Shuffling them around is also important for how I visualize the data, so that’s part of why I don’t just hide them.

As far as the code goes, which is where I’m really stuck, this is how far I’ve gotten now:

// 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('Critical Info');

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: {
            'Order Number': csvRow.Order_Number,
            'Buyer': csvRow.Buyer_Username,
            'Item Number': csvRow.Item_Number,
            'Item Title': csvRow.Item_Title,
            'Quantity': csvRow.Quantity,

        }
    }));

    // 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);
    }
}

I now seem to be running into an issue where the header row isn’t being read because the csv file has a blank row as the header. Here’s an example of a file:

While the code I have technically runs, it doesn’t upload any data. It creates the correct amount of new rows in airtable, but nothing is in them.

[Andy_Cloke]

Fae:

hasHeaderRow

I’d imagine you want hasHeaderRow: false if there’s no header row in the CSV? But I’m not too sure.