i have these column that i want to work on
i have initial list of name on ItemDB!D3:D
and these 2 column Purchasing!E2:E and Sale!G14:G that have part of the name from ItemDB!D3:D, also on Purchasing!E2:E and Sale!G14:G i use data validation from list of ItemDB!D3:D.
Then i make a button with the name of updateValues, and i try to make a apps script so when i clicked the button, it will check cell that change on ItemDB!D3:D then after find the change, it will also change the cell on Purchasing!E2:E and Sale!G14:G
the problem here are it run fine on small dataset, but when i applied to larger dataset, it not working properly. So can anyone help me to give some input what i need to do now?
here are some of the example and the code i use.
example:
ItemDB!D3:D
Apple –> changed to AppleX
Lemon
Banana
.
Purchasing!E2:E
Apple –> changed to AppleX when Apple on ItemDB!D3:D changed
Apple –> changed to AppleX when Apple on ItemDB!D3:D changed
Lemon
.
Sale!G14:G
Banana
Lemon
Apple –> changed to AppleX when Apple on ItemDB!D3:D changed
function updateValues() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const itemDBSheet = ss.getSheetByName('ItemDB');
const purchasingSheet = ss.getSheetByName('Purchasing');
const saleSheet = ss.getSheetByName('Sale');
// Get current values from ItemDB!D3:D
const currentRange = itemDBSheet.getRange('D3:D' + itemDBSheet.getLastRow());
const currentValues = currentRange.getValues().flat();
// Load previous values from Script Properties (or initialize if not set)
const scriptProperties = PropertiesService.getScriptProperties();
let previousValuesString = scriptProperties.getProperty('previousValues') || '';
// Convert the string back to an array
let previousValues = previousValuesString ? previousValuesString.split(',') : [];
// Initialize previousValues with current values if empty
if (previousValues.length === 0) {
previousValues = [...currentValues];
}
// Get full ranges for Purchasing and Sale sheets
const purchasingRange = purchasingSheet.getRange(2, 5, purchasingSheet.getLastRow() - 1, 1);
const saleRange = saleSheet.getRange(14, 7, saleSheet.getLastRow() - 13, 1);
const purchasingValues = purchasingRange.getValues();
const saleValues = saleRange.getValues();
// Create maps for quick lookups
const purchasingMap = new Map(purchasingValues.map((value, index) => [value[0], index]));
const saleMap = new Map(saleValues.map((value, index) => [value[0], index]));
let changesMade = false;
// Compare current and previous values
for (let i = 0; i < currentValues.length; i++) {
const currentValue = currentValues[i];
const previousValue = previousValues[i] || '';
if (currentValue !== previousValue) {
// Update Purchasing sheet
if (purchasingMap.has(previousValue)) {
const rowIndex = purchasingMap.get(previousValue);
purchasingValues[rowIndex][0] = currentValue;
changesMade = true;
}
// Update Sale sheet
if (saleMap.has(previousValue)) {
const rowIndex = saleMap.get(previousValue);
saleValues[rowIndex][0] = currentValue;
changesMade = true;
}
// Update the previousValues array with the current value
previousValues[i] = currentValue;
}
}
// Apply the changes to the sheets if any changes were made
if (changesMade) {
purchasingRange.setValues(purchasingValues);
saleRange.setValues(saleValues);
}
// Convert the updated previousValues array back to a string and store it in Script Properties
scriptProperties.setProperty('previousValues', previousValues.join(','));
}