Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 07, 2020 02:13 AM
Hello Brains!
I am looking for a way to quickly “fill inn the blanks” with the value of the above cell. Something like “if cell is empty, then copy value of above cell”. (This is an issue after my weekly import to my base)
I know I can drag down to copy - but when there are many lines downwards, the speed is increasing to a level I am not in control of, and I risk scrolling too far and end up overwriting a cell.
So I am stuck with ctrl+C, ctrl+V, but that makes a lot of manual work, and is a source for errors. (I also have coworkers in the base, so I need it to be easy, quick and safe).
Any tips, tricks, formulas or scripts?
Solved! Go to Solution.
Jul 07, 2020 05:07 PM
I don’t see any point in making a new thread, so I changed the category for this thread. Here’s a script that does the job, assuming that the record order hasn’t changed after creating the records. Remove (or comment out) the output.text()
lines inside the loop if you don’t want to see the progress.
let mytable = base.getTable("Table Name Here");
let query = await mytable.selectRecordsAsync();
let previous = "";
for (let record of query.records) {
let current = record.getCellValue("Field Name Here");
if (current) {
previous = current;
output.text(`New value found: ${current}`);
} else {
output.text(`Filling field with previous value: ${previous}`);
await mytable.updateRecordAsync(record, {"Field Name Here": previous})
}
}
output.text("Finished!");
Before and after images:
Jul 07, 2020 06:24 AM
For new records: If you group your records by that field, Airtable will automatically fill in the same value for any new records that you create within each grouping.
For existing records: To paste into existing fields that are contiguous, you’ve already tried the drag method, which is one way of doing this. The other method is to simply select all the contiguous fields you want to paste into by shift-clicking on the first field & the last field — and then press Control-V to paste into those contiguous fields. (Unfortunately, Airtable does not give us a way to select non-contiguous cells, although it doesn’t seem like you need non-contiguous cells in your example.)
Jul 07, 2020 09:10 AM
Indeed, this is precisely why Script Blocks were created; the pathway to data cleansing and automated normalization. You need a script block that can be run to update all data imperfections based on whatever collection of rules you may have.
This requires a little programming skill, but there are many people here who will do this to expand their knowledge. I suggest you post a variant of this request in the Script Block topic to get more focused attention.
Jul 07, 2020 12:07 PM
@Gro_Gravem If you’d like to pursue the scripting route, I’d suggest changing the category where your topic is posted to the scripting block category.
Jul 07, 2020 05:07 PM
I don’t see any point in making a new thread, so I changed the category for this thread. Here’s a script that does the job, assuming that the record order hasn’t changed after creating the records. Remove (or comment out) the output.text()
lines inside the loop if you don’t want to see the progress.
let mytable = base.getTable("Table Name Here");
let query = await mytable.selectRecordsAsync();
let previous = "";
for (let record of query.records) {
let current = record.getCellValue("Field Name Here");
if (current) {
previous = current;
output.text(`New value found: ${current}`);
} else {
output.text(`Filling field with previous value: ${previous}`);
await mytable.updateRecordAsync(record, {"Field Name Here": previous})
}
}
output.text("Finished!");
Before and after images:
Jul 08, 2020 01:18 AM
WOW - That was quick!
It is exactly what was I was looking for. And it works like a dream; I can just sit back and follow the empty cells being filled.
Thank you ❤️