Help

Run a script then delete data in a particular column

Topic Labels: Automations
Solved
Jump to Solution
1053 8
cancel
Showing results for 
Search instead for 
Did you mean: 
aundre_hamilton
5 - Automation Enthusiast
5 - Automation Enthusiast

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,
});
}
}
1 Solution

Accepted Solutions
aundre_hamilton
5 - Automation Enthusiast
5 - Automation Enthusiast

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

See Solution in Thread

8 Replies 8
Sho
11 - Venus
11 - Venus

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");

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
11 - Venus
11 - Venus

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

 

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

 

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
11 - Venus
11 - Venus

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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
11 - Venus
11 - Venus

That is indeed ChatGPT, btoa implemented!

You maybe could have asked this.

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

 

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. 😅