Script Block: Rank Descending and Update Field

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!

5 Likes

Dude, this is incredible. I built a gamification system on Airtable, but not having a RANK function sucks and I’ve never been able to gave a basic rank number for my participants. Do you think it would work with other fields as tiebreakers?

Hi guys, I’ve updated the script in the base with the following changes:

  1. Now allows users to rank by two fields in the order that the user selects the fields to rank by, thus handling the tiebreaking scenario mentioned in @Team_Upp’s post.
  2. Now uses updateRecordsAsync instead of updateRecordAsync. The previous version of this script would exceed the 30 seconds of Automation script run time if there were a lot of fields to update; with this change that should no longer be an issue. (It’s also a lot faster in general)

===

Big thanks and shoutout to @Stephen_Suen whose post I learned how to use updateRecordsAsync from.

Big thanks also to @Team_Upp who helped with research and without whom these improvements would not have been made.

As always, all mistakes are my own.

Thanks!

//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, pick another field to rank by after that, and then rank them 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 update with the rank number
let fieldToUpdate = await input.fieldAsync("Select the field you want updated with the rank number.  (Pick '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")



//Allow the user to choose the first field they want to rank
let firstFieldToRank = await input.fieldAsync("Select the first field you want to rank by.  Please pick a number, percent, or currency field.  (Pick 'Amount')", table);

//You can replace the above line with the following if you know which field you're going to be ranking by
//let firstFieldToRank = table.getField("[FIELD NAME]")
//For the purposes of this example base, it would be:
//let firstFieldToRank = table.getField("Amount")

//Check whether the firstFieldToRank 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 (firstFieldToRank.type === "number" || firstFieldToRank.type === "percent" || firstFieldToRank.type === "currency"){


	//Allow the user to choose the second field they want to rank
	let secondFieldToRank = await input.fieldAsync("Select the second field you want to rank by.  Please pick a number, percent, or currency field.  Pick ('Customers')", table);

	//You can replace the above line with the following if you know which field you're going to be ranking by
	//let secondFieldToRank = table.getField("[FIELD NAME]")
	//For the purposes of this example base, it would be:
	//let secondFieldToRank = table.getField("Customers")

	//Check whether the secondFieldToRank 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 (secondFieldToRank.type === "number" || secondFieldToRank.type === "percent" || secondFieldToRank.type === "currency"){


		//Shows the user some text so they know something's happening
		output.text(`Running...`);


		//Create an empty array.  We'll be using this to update records in batches of 50.
		let updates = [];

		//Selects the records from the table and then sorts it by descending
		let query = await table.selectRecordsAsync({
		    sorts: [
		        {field: firstFieldToRank, direction: "desc"},
		        {field: secondFieldToRank, 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++
			//Add the ID of the row, the name of the column to update, and the value of the row's cell to the "update" array
			updates.push({
		        id: record.id,
		        fields: {
		            [fieldToUpdate.id]: 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...`);
			}
			
		}

		// Update records in batches of 50.
		while (updates.length > 0) {
		    await table.updateRecordsAsync(updates.slice(0, 50));
		    updates = updates.slice(50);
		}

		//Show a "Done" message to the user once the loop is complete
		output.text(`Done!`);
	}
	else{
		output.text(`Sorry, the field you selected (${secondFieldToRank.name}) is not a number, percent, or currency field. Please start over.`);
	}
}
else{
	output.text(`Sorry, the field you selected (${firstFieldToRank.name}) is not a number, percent, or currency field. Please start over.`);
}
//Feedback form in case you want something fixed: https://airtable.com/shrXdwsEXSsd02oMI

Adam has been nothing short of incredible in helping me out and the scripts he wrote always worked flawlessly. This is probably a great use case to be replicated to other users.

Thanks again for helping so much and sharing with the community!