Script for Airtable scripting block - sets values in multiple cells based on a template record

SetupBlankCells.js

/*******************************************************************************
Title: Setup blank cells for Airtable scripting block
Author: Kuovonne Vorderbruggen
Date Created: March 2, 2020
Version: 1.0
Copyright (c) 2020 by Kuovonne Vorderbruggen
Usage License: MIT License

## Description
This script is designed for use in the Scripting Block of an
(Airtable)[airtable.com] database.

It bulk updates blank fields in any table to match
the values in a template record in the same table.


## Airtable Requirements
- Scripting Block installed
- Editor or higher permissions

## How To Use This Script
1. Open an Airtable base with the Scripting Block installed.
2. Copy and paste this script into the code editor.
3. Run the script and follow the instructions.

This script requires no customization.
This script requires no changes to the structure of the database.


********************************************************************************
*/
/******************************************************************************
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
********************************************************************************
*/
/*******************************************************************************
  DO NOT CHANGE ANYTHING IN THIS FILE
*******************************************************************************
*/
/*******************************************************************************
Helper Functions
********************************************************************************
*/
// Get the values from a field and put them in an object
// for easier access later
function getFieldValues(record, fieldDfns) {
  let fields = {};
  fieldDfns.forEach((fieldDfn) => {
    let fieldName = fieldDfn.name;
    let value = record.getCellValue(fieldName);
    let valueAsString = record.getCellValueAsString(fieldName);
    if (value) {
      fields[fieldName] = value;
      fields[fieldName + "AsString"] = valueAsString;
    }
  });
  return fields;
}

/*******************************************************************************
Welcome and Instructions
********************************************************************************
*/
output.markdown("# Setup blank cells \
\n\n \
This script sets values in blank cells based on a template of your choice. \
\n\n \
It can be used immediately with **no code editing** \
and **no design changes** to the base. \
\n\n \
Because it performs updates in bulk, \
it can change a large number of cells very quickly with **no undo**. \
\n\n \
Do not use this script on your actual data \
unless you are sure you understand how it works. \
\n\n \
**-->USE AT YOUR OWN RISK<--** \
\n\n \
");
await input.buttonsAsync("", ["Continue"]);

output.clear();
output.markdown("# Setup blank cells \
\n\n \
### Steps to using this script \
\n\n \
\n\n \
1. Setup a template record (described on next screen) in the table. \
(This may already be done.) \
\n\n \
2. Follow the prompts to pick the table and  template record. \
\n\n \
3. Preview the fields that will be changed. \
\n\n \
4. Update the records! \
\n\n \
\n\n \
");
await input.buttonsAsync("", ["Continue"]);

output.clear();
output.markdown("# Setup blank cells \
\n\n \
### Setting up the template record \
\n\n \
The template record can be **any record** in the table \
that has the values that you want copied into blank fields. \
You can use an existing record, or create a new one specifically \
to use as a template. \
\n\n \
If you do not want to set a value for a field, \
leave that field blank in the template record. \
\n\n \
\n\n \
");
await input.buttonsAsync("", ["Continue"]);

output.clear();
output.markdown("# Setup blank cells \
\n\n \
### Canceling \
\n\n \
If you want to stop this script at any time,  \
press the red *Stop* button in the upper right corner.  \
\n\n \
");

await input.buttonsAsync("", ["Continue"]);

/*******************************************************************************
Determine Table and Template Record
********************************************************************************
*/
output.clear();
output.markdown("# Setup blank cells ");
// Pick a table and get its field definations
let table = await input.tableAsync('Pick the table to update');
let editableFieldDfns = table.fields.filter((fieldDfn) => {
  return (! fieldDfn.isComputed);
});

// Pick a template record and get its values
let templateRecord = await input.recordAsync('Pick the template record', table);
let templateFields = getFieldValues(templateRecord, editableFieldDfns);
let targetFieldDfns = editableFieldDfns.filter((fieldDfn) => {
  return (templateFields[fieldDfn.name] != null);
});


/*******************************************************************************
Determine updates to do
Updates are done field by field, not record by record
********************************************************************************
*/

// Give user something to look at while gathering the info
output.clear();
output.markdown( "# Setup blank cells ");
output.markdown("### Determining cells to update ");
output.markdown( "Table: " + table.name);
output.markdown( "Template record: " + templateRecord.name);

// Gather all the records in the table and all the values
let queryResult = await table.selectRecordsAsync() ;
let allRecords = [];
queryResult.records.forEach((record) => {
  let fields = getFieldValues(record, editableFieldDfns);
  let newRecord = {"id": record.id, "name": record.name, "fields": fields};
  allRecords.push(newRecord);
});

// Combine field definitions, template values, and records into updates to do
// Store all updates in an indexed array
let updatesToDo = [];
targetFieldDfns.forEach((fieldDfn) => {
  // gather info about this field to update
  let fieldName = fieldDfn.name;
  let value = templateFields[fieldName];
  // Create a preview of the target value for this field
  let valuePreview = templateFields[fieldName + "AsString"];
  if (valuePreview.length > 40) {
    valuePreview = valuePreview.slice(0, 40) + " ...";
  }

  // get records where this field is blank
  let allRecordsWithBlanks = allRecords.filter((record) => {
    return (record.fields[fieldName] == null);
  });
  let recordsToUpdate = allRecordsWithBlanks.slice(0, 50);
  let recordsToUpdateNames = recordsToUpdate.map((record) => record.name);
  // consolidate info about changes for this field
  let update = { "fieldName": fieldName,
                    "value": value,
                    "recordsToUpdate": recordsToUpdate,
                    "valuePreview": valuePreview,
                    "countAllRecords": allRecordsWithBlanks.length,
                    "recordsToUpdateNames": recordsToUpdateNames,
                  };
  updatesToDo.push(update);
});

/*******************************************************************************
Display preview of changes and ask for confirmation before proceding
********************************************************************************
*/
// Create a preview of the updates to do
let previewInfo = updatesToDo.map((fieldList) => {
  let fieldPreview = {"field name": fieldList["fieldName"],
                      "template value": fieldList["valuePreview"],
                      "number records with field blank": fieldList["countAllRecords"],
                      "number of records to update (max 50)": fieldList["recordsToUpdate"].length,
                      "records": {"records to update": fieldList["recordsToUpdateNames"]},
                    };
  return fieldPreview;
});

// Display preview of changes and ask for confirmation
// First, make sure user had enough time to see previous screen
await input.buttonsAsync("Previewing changes will not change data.", ["Preview changes"]);
await output.clear();
output.markdown(" \
# Setup blank cells \
\n\n \
Here is a preview of the updates about to be made. \
\n\n \
If you want to stop this script before updating any records,  \
press the red *Stop* button in the upper right corner.  \
\n\n \
");
output.table(previewInfo);

// Ask for confirmation to proceed
await input.buttonsAsync("Update records at your own risk. There is NO UNDO.", ["Update records"]);

/*******************************************************************************
Perform the Updates
********************************************************************************
*/
output.clear();
output.markdown("# Setup blank cells ");
output.markdown("# Performing updates ");

updatesToDo.forEach( async (update) => {
  let fieldName = update.fieldName;
  let value = update.value;
  let count = update["recordsToUpdate"].length;
  let records = update["recordsToUpdate"].map((record) => {
    let recordForUpdate =  {id: record.id,
                  fields: {[fieldName]: value}
                  };
    return recordForUpdate;
  });
  output.text(`Updating ${fieldName} for ${count} records in ${table.name}.`);
  let answer = await table.updateRecordsAsync(records);
});

output.markdown("## Done updating records!");
// TODO: Provide some statistics on the update
//       For example: Updated # fields across # records in # seconds


/*******************************************************************************
END
********************************************************************************
*/