Feb 28, 2020 07:03 AM
Hi. I have no scripting experience so hoping someone can help. Is there a way to use the scripting block to delete all records from certain tables? I can do this manually, but I have to do this from 8 tables out of 30 in a base that I duplicate regularly. If there was just one button I can click to do this at once, that would be amazing. Bonus if it first asked to confirm that I really want to do this, so someone doesn’t accidentally click it.
Apr 24, 2020 08:31 AM
@Jeremy_Oglesby Great work used part of this for my solution. But got stuck trying to filter records based on cell data. how would you filter records before deleting them.
Apr 24, 2020 09:06 AM
Hi @Peter_Versaci – glad it was helpful!
I answered a similar question for someone else recently. See here:
Jun 09, 2020 12:55 AM
This was really helpful @Jeremy_Oglesby - thanks!
I was going to merge this into another script that populates a table, but as you’ve abstracted it so nicely it feels more sensible (and useful) to keep it separate. Cheers!
Jun 13, 2020 12:23 AM
Hi @Jeremy_Oglesby I think I understand your code to delete records, but I’m obviously missing something fundamental. My script does not recognise the text “batchAnd”. Does this need to be predefined somewhere? Or am I missing something?
Here’s my code followed by the error message. I’m trying to delete all the records in mediatable that are linked to a particular film, where the name of the link column in film table is “Media”.
let existing = await film.getCellValue("Media");
let recordsToDelete = existing.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', mediatable, recordsToDelete);
Jun 13, 2020 04:27 PM
Hi @Peter_Borg,
batchAnd()
is a convenience function I wrote for performing record creation, updates, or deletes on more than 50 records at a time.
In order to call it from your script, you have to copy and paste the entire function into your script, because it doesn’t exist in Airtable’s Scripting Block API.
You’ll find the entire async function near the bottom of my script above, but here is a link to where I’ve posted it on its own in the forums, along with an explanation — just copy and paste the entire function into your script (I usually place it at the very bottom):
Jun 14, 2020 09:55 PM
Thanks @Jeremy_Oglesby, all makes sense now.
Aug 11, 2020 04:41 AM
Hi! I’m new to scripting. I’ve played around and searched around for about a half hour; but i’d like to add the option to choose a view before deleting. Could you help a gal out? I’m referring to your top script :slightly_smiling_face:
Aug 24, 2020 01:41 AM
Here’s the script I put together to solve my problem:
let table = base.getTable("Tasks");
let view = table.getView(“ :curly_loop: Closed”);
let query = await view.selectRecordsAsync();
let records = query.records;
{
let recordsToDelete = records.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', table, recordsToDelete);
};
// ********************
async function batchAnd(action, table, records) {
let recordsActedOn = records.length;
switch (action) {
case ‘Update’:
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Create':
while (records.length > 0) {
await table.createRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Delete':
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
}
Mar 08, 2021 08:27 AM
Hi Jeremy! I am working on importing and managing data of device details on the cloud with API. I am a beginner and don’t have experience with coding. I was wondering if you can help me because my team is struggling with the data management of our clients while waiting on developing new tools (released in 5 weeks). Old tool is broken at the moment…
We have airtable that can download (to airtable) and upload (to the server) info for the devices (4 fields) on airtable sheet. Using a script for that. I also saw this script of yours that can delete all data from airtable sheet, but is it possible to use this script with the modification that deletes corresponding data on the server using api?
For example:
I would really appreciate your help or forward me to the place where I can find similar scripts. Thank you.
EDIT:
So far I have this script for updating records but it asks me to select a specific record or row in the table, I would like to not ask me, just replace/update all records in the table to server - checking them and update them or delete them.
let auth_headers = new Headers();
auth_headers.append('Accept',"application/json, text/plain, */*");
auth_headers.append('Content-Type',"application/json, text/plain, */*");
let UpdateYesNo = await input.buttonsAsync('Are you sure you want to update this record?', [
{label: 'Yes', variant: 'primary'},
{label: 'Cancel'},
]);
if (UpdateYesNo === 'Yes'){
let tableId = cursor.activeTableId;
let table = base.getTable(tableId);
let record = await input.recordAsync('', table);
output.clear()
if (table.name === 'LOCATIONS-DOWNLOAD'){
await updateLocation(record);
}else if(table.name === 'LOCATORS-DOWNLOAD'){
await updateLocator(record);
}else if(table.name === 'TAGS-DOWNLOAD'){
await updateTag(record);
}else if(table.name === 'ASSETS-DOWNLOAD'){
await updateAsset(record);
};
}else{
output.markdown('_Canceled_')
}
// _________________________________________________________________________________________
// FUNCTION FOR LOGIN TO THE WEBSITE
async function login(){
output.markdown('Please wait...')
let table = base.getTable('CREDENTIALS');
let records = await table.selectRecordsAsync();
let record = records.getRecord(records.recordIds[0]);
let username = record.getCellValue("Username");
let password = record.getCellValue("Password");
let login_url = 'https://api.servername.com/login';
let creds = {"username": username, "password": password};
let apiResponse = await fetch(login_url, {method:"POST", body:JSON.stringify(creds)});
let user_data = await apiResponse.json();
if (apiResponse.status === 200){
let token = user_data['Token'];
auth_headers.append('token',token);
return true
}else{
output.markdown(`* ${user_data['message']}*`);
return false
}
}
async function updateLocation(record){
let login_status = await login()
if (login_status){
let url = "https://api.servername2.com/location/"
let location_type
let payload = {}
if (record.getCellValue('Location Type').name === 'Active'){location_type = 0}else{location_type =1}
payload['LocationId'] = record.getCellValue('ID');
payload['LocationName'] = record.getCellValue('Location Name')
payload['LocationCode'] = record.getCellValue('Location Code')
payload['LocationType'] = location_type
let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers, body: JSON.stringify(payload)});
let responsText = await apiResponse.text()
let feedback = responsText.replace(/["':{}]/g, "").replace('message','');
if (feedback === 'OK'){
output.clear()
output.markdown('Record updated successfuly.')
}else{
output.markdown(feedback)
}
}
};
async function updateLocator(record){
let login_status = await login()
if (login_status){
let url = "https://api.servername2.com/locator/"
let payload = {}
let locator_type
if (record.getCellValue('Type').name === 'Wi-Fi'){locator_type = 3}else if(record.getCellValue('Type').name === 'Mobile'){locator_type =1}else{locator_type=2}
payload['LocatorName'] = record.getCellValue('Locator Name');
payload['LocatorId'] = record.getCellValue('MAC')
payload['LocatorType'] = locator_type
payload['LocatorSerialNumber'] = record.getCellValue('Serial Number')
payload['LocatorImsi'] = record.getCellValue('Locator IMSI')
let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers, body: JSON.stringify(payload)});
let responsText = await apiResponse.text()
let feedback = responsText.replace(/["':{}]/g, "").replace('message','');
if (feedback === 'OK'){
output.clear()
output.markdown('Record deleted successfuly.')
}else{
output.markdown(feedback)
}
}
};
async function updateTag(record){
let login_status = await login()
if (login_status){
let url = "https://api.servername2.com/tag/"
let tag_type
if (record.getCellValue('Type').name === 'Mobile'){tag_type = 1}else {tag_type=2}
let payload = {"TagId": record.getCellValue('MAC'), "NFCId": record.getCellValue('NFC'), "TagType": tag_type};
let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers});
let responsText = await apiResponse.text()
let feedback = responsText.replace(/["':{}]/g, "").replace('message','');
if (feedback === 'OK'){
output.clear()
output.markdown('Record deleted successfuly.')
}else{
output.markdown(feedback)
}
}
};
async function updateAsset(record){
let login_status = await login()
if (login_status){
let url = "https://api.servername2.com/asset/" + record.getCellValue('Asset ID')
let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers});
let responsText = await apiResponse.text()
let feedback = responsText.replace(/["':{}]/g, "").replace('message','');
if (feedback === 'OK'){
output.clear()
output.markdown('Record deleted successfuly.')
}else{
output.markdown(feedback)
}
}
};
Jun 02, 2022 01:57 PM
Hello years later! Is there any way to run this script through Zapier like on a schedule?