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!

Hi Adam,

I have a database of ski resorts, and calculated a raw score for each one. I’m trying to run this, but am having the issue that the value is stored in a formula field, not a number field.

Any thoughts on how to resolve this?

Thanks,

Hugo

Hi Hugo,

Ah, sorry about that. I had a check in place that would only allow the script to run if the selected field to rank by was a number, percent, or currency field. I’ve now added formula fields to that check, so it should run fine for you.

The original base has been updated, and the changes and new code can be found below.

===
Changes

  1. “Formula” fields can now also be used for ranking
    - Updates to the firstFieldToRank.type and secondFieldToRank.type if checks and the associated error text

===
New Code

//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, currency or formula 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, currency, or formula 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" || firstFieldToRank.type === "formula"){


	//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, currency or formula 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, currency, or formula 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" || secondFieldToRank.type === "formula"){


		//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, currency or formula field. Please start over.`);
	}
}
else{
	output.text(`Sorry, the field you selected (${firstFieldToRank.name}) is not a number, percent, currency or forumla field. Please start over.`);
}
//Feedback form in case you want something fixed: https://airtable.com/shrXdwsEXSsd02oMI