Run a script then delete data in a particular column

[aundre_hamilton]

Hey community, Aundre here. I hope you're all doing well!

I found this script on YouTube that works once you're on any table view where applicable. I need to automate it to work without being on a specific view. When I run it from automations, it does not work, I get the error below.

The automation I am trying to create is to run whenever a record enters a view. It should run the script which compresses images in the IMAGE column via TinyPNG, and then output them in IMAGE COMPRESSION. However, I find that I have to be on the view itself, click 'run script' for it to work. The automation however is not running from a view standpoint, hence why I believe it's giving an error.

After the image has been compressed, I'd like the IMAGE column to clear (delete) the original image, leaving only the compressed one. I've been able to do a simple automation for this by making the action an Update record and leaving the IMAGE field blank. However, I need it to be in one where it will clear the original image after the script runs. I'd allow a 20 second delay before clearing the original image, just to ensure that the image(s) upload(s) to TinyPNG.

I'm hoping this can work! Thank you community!

The errors:

let table = base.getTable(cursor.activeTableId); I get an error here and...

let view = table.getView(cursor.activeViewId); here... which says...

ReferenceError: cursor is not defined

at main on line 8

The YouTube video:

https://www.youtube.com/watch?v=OAXm5-gqBT8

The script:

// Modify these to your own values.

let tinyPngApiKey = 'xxxxxxxxxxxxxxxxxxxxxxx';

let airtableAttachmentSource = 'Image';

let airtableAttachmentDestination = 'Image Compression';

let airtableColumnToLog = 'Image';

// Don't change these unless you know what you're doing.

let table = base.getTable(cursor.activeTableId);

let view = table.getView(cursor.activeViewId);

let queryResult = await view.selectRecordsAsync();

for (let record of queryResult.records) {

let attachments = record.getCellValue(airtableAttachmentSource);

let compressedImageUrls = [];

if (attachments && attachments.length > 0) {

// Iterate through each attachment in the field.

for (let [i, attachment] of attachments.entries()) {

let recordAttachmentUrl = attachment['url'];

console.log(`Compressing ${record.getCellValue(airtableColumnToLog)} (Image ${i + 1})`);

let request = await remoteFetchAsync('https://api.tinify.com/shrink', {

body: JSON.stringify({'source': {'url': recordAttachmentUrl}}),

headers: {

Authorization: 'Basic ' + btoa('api:' + tinyPngApiKey),

'Content-Type': 'application/json'

},

method: 'POST'

})

const json = await request.json();

// Checks that the API didn't fail.

if (request.status == 201) {

let percentReduced = Math.round((1 - json.output.ratio) * 100);

let kbReduced = (json.input.size - json.output.size) / 1024;

console.log('Panda just saved you ' + percentReduced + '% (' + Math.round(kbReduced) + 'KB).');

// Add the compressed image URL to the array.

compressedImageUrls.push({ url: json['output']['url'] });

}

}

// Update the record with all the compressed image URLs.

await table.updateRecordAsync(record.id, {

[airtableAttachmentDestination]: compressedImageUrls,

});

}

}

[Sho]

Hi @aundre_hamilton,

Unfortunately, this script is for extensions and does not work with automation.

How about trying it by specifying the table name and view name directly?

let table = base.getTable("Table Name");
let view = table.getView("View Name");

[aundre_hamilton]

Thank you Sho! No more errors there! Now it's at line 23, saying that 'remoteFetchAsync is not defined'. What do you suggest here?

let request = await remoteFetchAsync('https://api.tinify.com/shrink', {

[Sho]

Yes, there is no remoteFetchAsync function in the script action.
How about this.

let request = await fetch('https://api.tinify.com/shrink', {

[aundre_hamilton]

No more errors there! I hope this next one is the last one. I realized that wherever there is an error, even before running the script, it has a red wrinkled underline. It's similar to spelling a word incorrectly or having a grammatical error.  So, now 'btoa is not defined'.

Authorization: 'Basic ' + btoa('api:' + tinyPngApiKey),

Any solutions here?

[Sho]

Here is the solution.
It looks like you need to get the value from the extension once.

Solved: Re: ERROR: btoa is not defined - Airtable Community

[aundre_hamilton · Accepted solution]

Hey Sho, I used ChatGPT to fix the btoa error and it worked! When I first started off, it gave me errors and I just copied and pasted the errors and kept trying. I was about to give up and move on, and then I pasted the last error and it seemed as though ChatGPT was getting frustrated on its own and wrote me a very long code which just happened to work! 😅 See the code below! I just want to say thank you once again! You started me off, which really narrowed down the issues to this solution! 5-stars Sho!

// Replace 'xxx' with your actual TinyPNG API key

const tinyPngApiKey = 'xxx';

const airtableAttachmentSource = 'Image';

const airtableAttachmentDestination = 'Image Compression';

// Access the "Inventory - Factory" table and "Image Compression" view

const table = base.getTable('Inventory - Factory');

const view = table.getView('Image Compression');

// Retrieve records from the specified view

const queryResult = await view.selectRecordsAsync();

// Function to encode a string to Base64

function encodeToBase64(str) {

const base64Chars =

'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';

let result = '';

for (let i = 0; i < str.length; i += 3) {

const group = (str.charCodeAt(i) << 16) | (str.charCodeAt(i + 1) << 8) | str.charCodeAt(i + 2);

result +=

base64Chars.charAt((group >> 18) & 63) +

base64Chars.charAt((group >> 12) & 63) +

base64Chars.charAt((group >> 6) & 63) +

base64Chars.charAt(group & 63);

}

return result;

}

// Function to compress and save images for a record

async function compressAndSaveImages(record) {

const attachments = record.getCellValue(airtableAttachmentSource);

let compressedImageUrls = [];

if (attachments && attachments.length > 0) {

// Iterate through each attachment in the field

for (let [i, attachment] of attachments.entries()) {

const recordAttachmentUrl = attachment['url'];

console.log(`Compressing ${record.getCellValue(airtableAttachmentSource)} (Image ${i + 1})`);

const apiKey = 'api:' + tinyPngApiKey;

const encodedApiKey = encodeToBase64(apiKey);

const request = await fetch('https://api.tinify.com/shrink', {

method: 'POST',

headers: {

'Content-Type': 'application/json',

'Authorization': 'Basic ' + encodedApiKey,

},

body: JSON.stringify({ source: { url: recordAttachmentUrl } }),

});

const json = await request.json();

// Checks that the API didn't fail

if (request.status == 201) {

const compressedImageUrl = json.output.url;

compressedImageUrls.push({ url: compressedImageUrl });

}

}

// Update the record with all the compressed image URLs

await table.updateRecordAsync(record.id, {

[airtableAttachmentDestination]: compressedImageUrls,

});

console.log(`Images compressed and saved for record: ${record.id}`);

}

}

// Process each record in the view

for (const record of queryResult.records) {

await compressAndSaveImages(record);

}

[Sho]

That is indeed ChatGPT, btoa implemented!

You maybe could have asked this.

convert the following with the javascript btoa function.　api:xxx

[aundre_hamilton]

The thing is, I am not a programmer at all. I am just good at Excel type formulas. So, turning to ChatGPT was just me taking a chance and if it didn't work, I'd have left it. 😅