Ranks your data in descending order based on a field you select, and updates a field you select with the ranking number.
Here’s a link to an example base which you can copy and try for yourself: https://airtable.com/shr1Ymp6Z39jsN1Pq
//This script will grab the rows from the chosen table, rank them, and then update the chosen column
output.text("This code will let you pick a field you want your rows ranked by, and then rank it in descending order.");
//Allow the user to choose the table they want to manipulate
let table = await input.tableAsync('Select the table');
//You can replace the above line with the following if you know what table you're going to be using
//let table = base.getTable("[TABLE NAME]")
//For the purposes of this example base, it would be:
//let table = base.getTable("Sales");
//Allow the user to choose the field they want to rank
let fieldToRank = await input.fieldAsync("Select the field you want to rank by. Please pick a number, percent, or currency field.", table);
//You can replace the above line with the following if you know which field you're going to be ranking by
//let fieldToRank = table.getField("[FIELD NAME]")
//For the purposes of this example base, it would be:
//let fieldToRank = table.getField("Amount")
//Check whether the fieldToRank is a number, percent, or currency field to ensure it works as intended. If it's not, stop the code and inform the user why
if (fieldToRank.type === "number" || fieldToRank.type === "percent" || fieldToRank.type === "currency"){
//Allow the user to choose the field they want to update with the rank number
let fieldToUpdate = await input.fieldAsync("Select the field for the rank number", table);
//You can replace the above line with the following if you know which field you're going to be updating with the rank number
//let fieldToUpdate = table.getField("[FIELD NAME]")
//For the purposes of this example base, it would be:
//let fieldToUpdate = table.getField("Rank Number")
//Shows the user some text so they know something's happening
output.text("Running...");
//Selects the records from the table and then sorts it by descending
let query = await table.selectRecordsAsync({
sorts: [
{field: fieldToRank, direction: "desc"},
]
});
//Create a variable that we will update to get the rank number
let rankNumber = 0;
//Since it has already been sorted, all we need to do is create the rank number as we loop through the result
for (let record of query.records) {
//Add 1 to the rank number
rankNumber++
//Update the selected field of the row with the rank number
await table.updateRecordAsync(record.id, {
[fieldToUpdate.name]: rankNumber,
})
//Whenever the loop runs three times, show a "Still running" message to the user so that they know something is still happening
if (rankNumber % 3 === 0 ){
output.text("Still running...");
}
//Show a "Done" message to the user once the loop is complete
if (rankNumber === query.records.length){
output.text("Done!");
}
}
}
else{
output.text("Sorry, the field you selected is not a number, percent, or currency field. Please start over.");
}
//Feedback form in case you want something fixed: https://airtable.com/shrXdwsEXSsd02oMI
It’s really basic, but I wanted to put it out there in case someone found it useful.
Apologies if it’s messy / oddly formatted; I don’t have much experience with development and I think I may have went a bit too far with my commenting.
This was heavily inspired / influenced by JonathanBowen’s Script Block: Find & Replace Text, so a lot of the formatting / concepts will look familiar to his work (Big shout out and thank you to him). That said, all mistakes are my own.
Thank you!
