Randomize Values
Generate random data for empty fields across multiple data types with customizable ranges and constraints
This script allows you to populate empty fields in your NocoDB tables with random values based on the field type. It supports various data types including numbers, dates, selections, and more, ensuring that the generated data is appropriate for each field's requirements.
let settings = input.config({
title: "Randomize Field Values",
description: `This script will randomize values for a selected field. The following field types are supported:
- Number
- Currency
- Percent
- Rating
- Date
- User
- Single/Multiple Select`,
items: [
input.config.table("selectedTable", { label: "Select Table" }),
input.config.field("selectedField", {
parentTable: "selectedTable",
label: "Field to Randomize",
}),
],
});
let { selectedTable, selectedField } = settings as {
selectedTable: Table,
selectedField: Field
};
output.text(`Starting randomization of '${selectedField.name}' field in '${selectedTable.name}' table...`);
let valueRange: Record<string, any>;
switch (selectedField.type) {
case UITypes.Currency: {
valueRange = await getValueRange(async () => {
let minValue = await promptForNumber("Enter the minimum currency value:");
let maxValue = await promptForNumber("Enter the maximum currency value:");
return await confirmValueRange(minValue, maxValue);
});
await updateSelectedField((record) => randomBetween(valueRange.min, valueRange.max, identity));
break;
}
case UITypes.Date: {
valueRange = await getValueRange(async () => {
let earliestDate = await promptForDate("Enter the earliest date:");
let latestDate = await promptForDate("Enter the latest date:");
return await confirmValueRange(earliestDate, latestDate);
});
await updateSelectedField((record) => randomDateBetween(valueRange.min, valueRange.max));
break;
}
case UITypes.DateTime: {
valueRange = await getValueRange(async () => {
let earliestDateTime = await promptForDate("Enter the earliest date/time:");
let latestDateTime = await promptForDate("Enter the latest date/time:");
return await confirmValueRange(earliestDateTime, latestDateTime);
});
await updateSelectedField((record) => randomDateBetween(valueRange.min, valueRange.max));
break;
}
case UITypes.MultiSelect: {
valueRange = await getValueRange(async () => {
let minSelections = await promptForNumber("Enter minimum number of selections:");
let maxSelections = await promptForNumber("Enter maximum number of selections:");
return await confirmValueRange(minSelections, maxSelections);
});
if (!selectedField.options) {
throw new Error('Multi-select field is missing options configuration');
}
const fieldOptions = selectedField.options;
if (fieldOptions.choices.length < valueRange.min) {
output.text(`❌ Error: Field only has ${fieldOptions.choices.length} choices, but minimum requirement is ${valueRange.min}.`);
break;
}
await updateSelectedField((record) => {
const availableChoices = fieldOptions.choices.map((choice) => choice.title);
let numberOfSelections = randomBetween(valueRange.min, Math.min(valueRange.max, availableChoices.length), Math.ceil);
let shuffledChoices = shuffleArray(availableChoices);
return shuffledChoices.slice(0, numberOfSelections);
});
break;
}
case UITypes.Number: {
valueRange = await getValueRange(async () => {
let minNumber = await promptForNumber("Enter the minimum number:");
let maxNumber = await promptForNumber("Enter the maximum number:");
return await confirmValueRange(minNumber, maxNumber);
});
await updateSelectedField((record) => randomBetween(valueRange.min, valueRange.max));
break;
}
case UITypes.Percent: {
valueRange = await getValueRange(async () => {
let minPercent = await promptForNumber("Enter the minimum percentage:");
let maxPercent = await promptForNumber("Enter the maximum percentage:");
return await confirmValueRange(minPercent, maxPercent);
});
await updateSelectedField((record) => randomBetween(valueRange.min, valueRange.max));
break;
}
case UITypes.Rating: {
if (!selectedField.options) {
throw new Error('Rating field is missing options configuration');
}
const ratingOptions = selectedField.options;
await updateSelectedField((record) => randomBetween(1, ratingOptions.max_value));
break;
}
case UITypes.User: {
await updateSelectedField(
(record) =>
base.activeCollaborators[
randomBetween(0, base.activeCollaborators.length, Math.floor)
]
);
break;
}
case UITypes.SingleSelect: {
if (!selectedField.options) {
throw new Error('Single-select field is missing options configuration');
}
const selectOptions = selectedField.options;
await updateSelectedField(
(record) =>
selectOptions.choices[randomBetween(0, selectOptions.choices.length, Math.floor)].title
);
break;
}
default: {
output.text(
`❌ Field '${selectedField.name}' (type: ${selectedField.type}) cannot be randomized with this script. Please select a supported field type or customize the script to handle this field type.`
);
break;
}
}
output.text("✅ Randomization complete! Run the script again to randomize another field.");
// Utility Functions
function identity(value) {
return value;
}
function randomBetween(min, max, roundingFunction = Math.round) {
return roundingFunction((max - min) * Math.random()) + min;
}
function shuffleArray(originalArray) {
let shuffledArray = originalArray.slice(0);
for (let i = shuffledArray.length - 1; i > 0; i--) {
const randomIndex = Math.floor(Math.random() * (i + 1));
[shuffledArray[i], shuffledArray[randomIndex]] = [shuffledArray[randomIndex], shuffledArray[i]];
}
return shuffledArray;
}
/**
* Generate a random date between two dates
* @param {Date} startDate - The earliest possible date
* @param {Date} endDate - The latest possible date
*/
function randomDateBetween(startDate, endDate) {
return new Date(Math.round(randomBetween(startDate.getTime(), endDate.getTime())));
}
async function promptForNumber(promptText) {
return Number(await input.textAsync(promptText));
}
async function promptForDate(promptText) {
return new Date(await input.textAsync(promptText));
}
async function confirmValueRange(minValue, maxValue) {
const confirmed = await input.buttonsAsync(
`Confirm randomization range: ${minValue} to ${maxValue}`,
[
{ label: "✅ Confirm", value: true },
{ label: "❌ Cancel", value: false },
]
);
if (confirmed) {
return { min: minValue, max: maxValue };
}
return null;
}
async function getValueRange(rangeInputFunction) {
let range;
do {
range = await rangeInputFunction();
} while (!range);
return range;
}
/**
* Update the selected field for all records that have empty values
* @param {(record: Record) => any} valueGeneratorFunction - Function to generate new values
*/
async function updateSelectedField(valueGeneratorFunction) {
let recordUpdates = [];
let processedCount = 0;
const recordQuery = await selectedTable.selectRecordsAsync({ fields: [selectedField] });
// Load all records
while (recordQuery.hasMoreRecords) {
await recordQuery.loadMoreRecords();
}
output.text(`📊 Processing ${recordQuery.records.length} records...`);
// Process records that have empty values
for (let record of recordQuery.records) {
const currentValue = record.getCellValue(selectedField);
if (!currentValue || (Array.isArray(currentValue) && currentValue.length === 0)) {
const newValue = valueGeneratorFunction(record);
recordUpdates.push({
id: record.id,
fields: { [selectedField.id]: newValue }
});
processedCount++;
}
}
if (recordUpdates.length === 0) {
output.text("ℹ️ No empty records found to update.");
return;
}
output.text(`🔄 Updating ${recordUpdates.length} records with empty values...`);
// Update records in batches of 10
for (let batchStart = 0; batchStart < recordUpdates.length; batchStart += 10) {
const batchEnd = Math.min(batchStart + 10, recordUpdates.length);
const currentBatch = recordUpdates.slice(batchStart, batchEnd);
output.text(`📝 Updating batch ${Math.floor(batchStart / 10) + 1}/${Math.ceil(recordUpdates.length / 10)} (records ${batchStart + 1}-${batchEnd})`);
await selectedTable.updateRecordsAsync(currentBatch);
}
output.text(`✅ Successfully updated ${recordUpdates.length} records.`);
}
Use Cases
- Demo Data: Create realistic sample data for presentations and testing
- Test Scenarios: Generate varied data sets for application testing
- Placeholder Content: Fill empty fields with appropriate dummy data
- Data Modeling: Create diverse datasets for analysis and development
Supported Field Types
Numeric Fields
- Number: Generate random integers or decimals within specified ranges
- Currency: Create random monetary values with proper decimal precision
- Percent: Generate percentage values between defined minimum and maximum
- Rating: Random ratings within the field's configured scale
Date Fields
- Date: Random dates within a specified date range
- DateTime: Random date and time combinations within defined boundaries
Selection Fields
- SingleSelect: Randomly choose from available options
- MultiSelect: Generate random combinations of available options
- User: Randomly assign from available collaborators
How it Works
- Select Field: Choose the field you want to populate with random values
- Configure Ranges: Set minimum and maximum values or date ranges as applicable
- Set Constraints: Define any specific parameters for the field type
- Preview Settings: Review your configuration before applying
- Generate Values: The script fills only empty cells, preserving existing data
Requirements
- Fields with empty values (existing data will not be overwritten)
- Appropriate field types from the supported list above
- Valid ranges or constraints for the selected field type
Configuration Examples
Number Field
- Minimum: 1
- Maximum: 1000
Date Field
- Start Date: January 1, 2024
- End Date: December 31, 2024
Currency Field
- Minimum: $10.00
- Maximum: $500.00
Rating Field
- Scale: 1-5 stars (uses field's configured rating scale)
SingleSelect Field
- Options: Randomly selects from all available options in the field
Features
- Preserves Existing Data: Only fills empty cells, never overwrites existing values
- Type-Appropriate: Generates data that matches each field's specific requirements
- Customizable Ranges: Set meaningful boundaries for numeric and date fields
- Realistic Values: Creates data that looks natural and appropriate
- Batch Processing: Efficiently handles large numbers of records
Best Practices
- Set realistic ranges that match your data requirements
- Test with a small sample before applying to large datasets
- Consider your field validation rules when setting ranges
- Use meaningful date ranges for time-based data
- Review the generated data to ensure it meets your needs
Safety Features
- Non-Destructive: Existing data is completely preserved
- Empty Cells Only: Only populates fields that are currently empty
- Type Validation: Ensures generated values match field type requirements
- Range Compliance: All values respect the specified minimum and maximum bounds