There’s a apps script function I wrote, loadAccount() that moves data within multiple sheets, however Apps Script is bundling the operations in a way not optimal for user experience. Essentially what happens is that apps script is bundling everything up until the first categorySheet.getRange() call at the end. I noticed this in testing by undoing the script operation and observing that more than one undo is required to revert the changes. I want only one undo to be required to undo all changes in this function. Calling SpreadsheetApp.flush() at the end does not resolve this issue. I have another function, importData() that does not have this problem. I plan on adding more sheet operations for loadAccount() so I’d like to get to the bottom of this issue.
Why is the problem I’m observing in loadAccount() not happenning in importData()?
How can this be addressed?
function loadAccount() {
let account2Load = "Acc1";
if (account2Load == "") { return "<span style="font-weight: bold; color: red">Account Not Selected!</span>"; }
if (!isNaN(account2Load)) { return "<span style="font-weight: bold; color: red">Account Must Be A Word!</span>";}
if (!_account_contains(account2Load)) { return "<span style="font-weight: bold; color: red">Account Doesn't Exists!</span>"; }
const summarySheet = commonFields().summarySheet;
const transactionSheet = commonFields().transactionSheet;
const categorySheet = getAllCategories().sheet
const billSheet = getAllBills().sheet;
const targetSheet = getAllTargets().sheet;
const debtSheet = getAllDebts().sheet;
const creditSheet = getAllCredits().sheet;
const archiveSheet = getAllArchives().sheet;
const noteSheet = getAllNotes().sheet;
const summaryStartRow = _summary_start_row();
const summaryStartColumn = _summary_start_column();
const categoryColumn = _category_start_column();
const categoryElements = _category_num_elements();
const transactionColumn = _transaction_start_column();
const transactionElements = _transaction_num_elements();
const startRow = commonFields().startRow;
const accountRange = commonFields().accountRange;
const accountTitleRange = commonFields().accountTitleRange;
const transactionRange = commonFields().transactionRange;
const startBalanceRange = commonFields().startBalanceRange;
const inflowCarryoverRange = commonFields().inflowCarryoverRange;
const inflowAdjustRange = commonFields().inflowAdjustRange;
const categoryRange = getAllCategories().categorySheetRange;
const numAccounts = callGetAllAccountVals().length;
const startBalance = summarySheet.getRange(startBalanceRange).getValue();
const inflows = summarySheet.getRange(commonFields().inflows).getValues(); //REMEMBER TO REFACTOR INFLOWS ELSEWHERE!!!!!!!!!!
const accountTitle = summarySheet.getRange(accountTitleRange).getValue();
const transactions = transactionSheet.getRange(transactionRange).getValues();
const categories = categorySheet.getRange(categoryRange).getValues();
let accounts = summarySheet.getRange(accountRange).getValues().filter(account => account[0] != "");
let index2Load = accounts.map(row => row[0]).indexOf(account2Load);
let transactionsPerAccount = transactionSheet.getRange(startRow, transactionColumn, transactionSheet.getMaxRows() - startRow + 1, transactionElements * numAccounts).getValues();
let categoriesPerAccount = categorySheet.getRange(startRow, categoryColumn, categorySheet.getMaxRows() - startRow + 1, categoryElements * numAccounts).getValues();
const startBalance2Load = accounts[index2Load][1];
const inflowCarryover2Load = accounts[index2Load][2];
const inflowAdjustment2Load = accounts[index2Load][3];
const transactions2Load = transactionsPerAccount.map(row => row.splice(index2Load * transactionElements, transactionElements));
const categories2Load = categoriesPerAccount.map(row => row.splice(index2Load * categoryElements, categoryElements));
accounts.splice(index2Load, 1);
accounts.push([accountTitle, startBalance, inflows[0], inflows[2]]);
for (let i = 0; i < transactionsPerAccount.length; i++) { transactionsPerAccount[i].push(...transactions[i]); }
for (let i = 0; i < categoriesPerAccount.length; i++) { categoriesPerAccount[i].push(...categories[i]); }
summarySheet.getRange(accountTitleRange).setValue(account2Load);
summarySheet.getRange(startBalanceRange).setValue(startBalance2Load);
summarySheet.getRange(inflowCarryoverRange).setValue(inflowCarryover2Load);
summarySheet.getRange(inflowAdjustRange).setValue(inflowAdjustment2Load);
summarySheet.getRange(summaryStartRow, summaryStartColumn, accounts.length, accounts[0].length).setValues(accounts);
transactionSheet.getRange(transactionRange).setValues(transactions2Load);
transactionSheet.getRange(startRow, transactionColumn, transactionsPerAccount.length, transactionsPerAccount[0].length).setValues(transactionsPerAccount);
categorySheet.getRange(categoryRange).setValues(categories2Load);
categorySheet.getRange(startRow, categoryColumn, categoriesPerAccount.length, categoriesPerAccount[0].length).setValues(categoriesPerAccount);
SpreadsheetApp.flush();
return "<span style="font-weight: bold">" + '"'+ account2Load + "" Account Loaded</span>";
}
This one works as intended:
function importData() {
let ui = SpreadsheetApp.getUi();
let result = ui.alert(
"Please confirm",
"Are you sure you want to import?",
ui.ButtonSet.YES_NO,
);
// Process the user's response.
if (result == ui.Button.NO) {
// User clicked "No".
ui.alert("Import cancelled.");
return;
}
const destSummarySheet = commonFields().summarySheet;
const destCategoriesSheet = getAllCategories().sheet;
const destBillsSheet = getAllBills().sheet;
const destTargetsSheet = getAllTargets().sheet;
const destCreditsSheet = getAllCredits().sheet;
const destDebtsSheet = getAllDebts().sheet;
const destArchiveSheet = getAllArchives().sheet;
const destNoteSheet = getAllNotes().sheet;
const sourceIDRange = commonFields().sourceIDRange;
const sourceSheetID = destSummarySheet.getRange(sourceIDRange).getValue();
if (sourceSheetID == "") { ui.alert("Copy destination ID into source ID for import!"); return; }
const source = SpreadsheetApp.openById(sourceSheetID);
const sourceSummarySheet = source.getSheets().find(sheet => sheet.getSheetName() == "Summary");
const sourceCategoriesSheet = source.getSheets().find(sheet => sheet.getSheetName() == "Categories");
const sourceBillsSheet = source.getSheets().find(sheet => sheet.getSheetName() == "Monthly Bills");
const sourceTargetsSheet = source.getSheets().find(sheet => sheet.getSheetName() == "Savings Targets");
const sourceCreditsSheet = source.getSheets().find(sheet => sheet.getSheetName() == "Credit Accounts");
const sourceDebtsSheet = source.getSheets().find(sheet => sheet.getSheetName() == "Debts");
const sourceNotesSheet = source.getSheets().find(sheet => sheet.getSheetName() == "Notes");
const startRow = commonFields().startRow;
const startColumn = commonFields().startColumn;
const startBalanceRange = commonFields().startBalanceRange;
const endBalanceRange = commonFields().endBalanceRange;
const accountTitleRange = commonFields().accountTitleRange;
const inflowAdjustRange = commonFields().inflowAdjustRange;
const adjustmentRange = commonFields().adjustmentRange;
const categorySheetRange = getAllCategories().categorySheetRange;
const catOverflowValsRange = getAllCategories().overflowValsRange;
const billSheetRange = getAllBills().billSheetRange;
const billOverflowValsRange = getAllBills().overflowValsRange;
const targetSheetRange = getAllTargets().targetSheetRange;
const targetOverflowValsRange = getAllTargets().overflowValsRange;
const creditSheetRange = getAllCredits().creditSheetRange;
const creditBalanceRange = getAllCredits().creditBlanceRange;
const debtSheetRange = getAllDebts().debtSheetRange;
const debtRemainingBalanceValsRange = getAllDebts().remainingBalanceRange;
const archiveCategoryRange = getAllArchives().categoryArchiveRange;
const archiveBillRange = getAllArchives().billArchiveRange;
const archiveTargetRange = getAllArchives().targetArchiveRange;
const archiveCreditRange = getAllArchives().creditArchiveRange;
const archiveDebtRange = getAllArchives().debtArchiveRange;
const notesCategoryRange = getAllNotes().categoryRange;
const notesBillRange = getAllNotes().billRange;
const notesTargetRange = getAllNotes().targetRange;
const notesCreditRange = getAllNotes().creditRange;
let creditBillRange = getAllBills().creditRange;
let creditTargetRange = getAllTargets().creditRange;
let billRule = destBillsSheet.getRange(creditBillRange).getDataValidation();
let targetRule = destTargetsSheet.getRange(creditTargetRange).getDataValidation();
let sourceEndBalance = sourceSummarySheet.getRange(endBalanceRange).getValue();
let sourceTitle = sourceSummarySheet.getRange(accountTitleRange).getValue();
let sourceCarryoverAdjustment = sourceSummarySheet.getRange(inflowAdjustRange).getValue();
let sourceLumpAdjustment = sourceSummarySheet.getRange(adjustmentRange).getValue();
let sourceTransactionCategories = sourceCategoriesSheet.getRange(categorySheetRange).getValues().filter(category => category[0] != "");
let sourceCatOverflowVals = sourceCategoriesSheet.getRange(catOverflowValsRange).getValues().filter(overflow => overflow != "").flat();
let catZeros = Array(sourceTransactionCategories.length).fill().map(() => Array(1).fill(0)).flat(); //Fill allocation amounts with zeros
for (let i = 0; i < sourceTransactionCategories.length; i++) {
sourceTransactionCategories[i][1] = sourceCatOverflowVals[i];
sourceTransactionCategories[i][2] = catZeros[i];
}
let sourceTransactionBills = sourceBillsSheet.getRange(billSheetRange).getValues().filter(bill => bill[0] != "");
let sourceBillOverflowVals = sourceBillsSheet.getRange(billOverflowValsRange).getValues().filter(overflow => overflow != "").flat();
let billZeros = Array(sourceTransactionBills.length).fill().map(() => Array(1).fill(0)).flat(); //Fill allocation amounts with zeros
for (let i = 0; i < sourceTransactionBills.length; i++) {
sourceTransactionBills[i][3] = sourceBillOverflowVals[i];
sourceTransactionBills[i][4] = billZeros[i];
}
let sourceTransactionTargets = sourceTargetsSheet.getRange(targetSheetRange).getValues().filter(target => target[0] != "");
let sourceTargetOverflowVals = sourceTargetsSheet.getRange(targetOverflowValsRange).getValues().filter(overflow => overflow != "").flat();
let targetZeros = Array(sourceTransactionTargets.length).fill().map(() => Array(1).fill(0)).flat(); //Fill allocation amounts with zeros
for (let i = 0; i < sourceTransactionTargets.length; i++) {
sourceTransactionTargets[i][5] = sourceTargetOverflowVals[i];
sourceTransactionTargets[i][6] = targetZeros[i];
}
let sourceCreditAccounts = sourceCreditsSheet.getRange(creditSheetRange).getValues().filter(credit => credit[0] != "");
let sourceCreditBalances = sourceCreditsSheet.getRange(creditBalanceRange).getValues().filter(balance => balance[0].toString().length > 0);
let creditZeros = Array(sourceCreditAccounts.length).fill().map(() => Array(1).fill(0)).flat(); //Fill allocation amounts with zeros
let sourceAdjustmentSum = sourceCreditAccounts.map(row => row[2]).reduce((accum, curr) => accum + curr, 0);
for (let i = 0; i < sourceCreditAccounts.length; i++) {
sourceCreditAccounts[i][4] = sourceCreditBalances[i][3];
sourceCreditAccounts[i][3] = sourceCreditAccounts[i][3] + sourceCreditBalances[i][0] - sourceCreditAccounts[i][2];
sourceCreditAccounts[i][2] = creditZeros[i];
}
let sourceNoteCats = sourceNotesSheet.getRange(notesCategoryRange).getDisplayValues();
let sourceNoteBills = sourceNotesSheet.getRange(notesBillRange).getDisplayValues();
let sourceNoteTargets = sourceNotesSheet.getRange(notesTargetRange).getDisplayValues();
let sourceNoteCredits = sourceNotesSheet.getRange(notesCreditRange).getDisplayValues();
let sourceDebts = sourceDebtsSheet.getRange(debtSheetRange).getValues().filter(debt => debt[0] != "");
let sourceRemainingBalanceVals = sourceDebtsSheet.getRange(debtRemainingBalanceValsRange).getValues().filter(endBalance => endBalance != "").flat();
for (let i = 0; i < sourceDebts.length; i++) { sourceDebts[i][2] = sourceRemainingBalanceVals[i]; }
destBillsSheet.getRange(creditBillRange).setDataValidation(null);
destTargetsSheet.getRange(creditTargetRange).setDataValidation(null);
destCategoriesSheet.getRange(categorySheetRange).clearContent();
destBillsSheet.getRange(billSheetRange).clearContent();
destTargetsSheet.getRange(targetSheetRange).clearContent();
destCreditsSheet.getRange(creditSheetRange).clearContent();
destDebtsSheet.getRange(debtSheetRange).clearContent();
destArchiveSheet.getRange(archiveCategoryRange).clearContent();
destArchiveSheet.getRange(archiveBillRange).clearContent();
destArchiveSheet.getRange(archiveTargetRange).clearContent();
destArchiveSheet.getRange(archiveCreditRange).clearContent();
destArchiveSheet.getRange(archiveDebtRange).clearContent();
destNoteSheet.getRange(notesCategoryRange).clearContent();
destNoteSheet.getRange(notesBillRange).clearContent();
destNoteSheet.getRange(notesTargetRange).clearContent();
destSummarySheet.getRange(accountTitleRange).setValue(sourceTitle);
destSummarySheet.getRange(startBalanceRange).setValue(sourceEndBalance);
destSummarySheet.getRange(inflowAdjustRange).setValue(parseFloat(sourceCarryoverAdjustment) + parseFloat(sourceLumpAdjustment) - parseFloat(sourceAdjustmentSum));
if (sourceTransactionCategories.length > 0) {
destCategoriesSheet.getRange(startRow, startColumn, sourceTransactionCategories.length, sourceTransactionCategories[0].length).setValues(sourceTransactionCategories);
}
if (sourceTransactionBills.length > 0) {
destBillsSheet.getRange(startRow, startColumn, sourceTransactionBills.length, sourceTransactionBills[0].length).setValues(sourceTransactionBills);
}
if (sourceTransactionTargets.length > 0) {
destTargetsSheet.getRange(startRow, startColumn, sourceTransactionTargets.length, sourceTransactionTargets[0].length).setValues(sourceTransactionTargets);
}
if (sourceCreditAccounts.length > 0) {
destCreditsSheet.getRange(startRow, startColumn, sourceCreditAccounts.length, sourceCreditAccounts[0].length).setValues(sourceCreditAccounts);
}
if (sourceDebts.length > 0) {
destDebtsSheet.getRange(startRow, startColumn, sourceDebts.length, sourceDebts[0].length).setValues(sourceDebts);
}
destNoteSheet.getRange(notesCategoryRange).setValues(sourceNoteCats);
destNoteSheet.getRange(notesBillRange).setValues(sourceNoteBills);
destNoteSheet.getRange(notesTargetRange).setValues(sourceNoteTargets);
destNoteSheet.getRange(notesCreditRange).setValues(sourceNoteCredits);
destBillsSheet.getRange(creditBillRange).setDataValidation(billRule);
destTargetsSheet.getRange(creditTargetRange).setDataValidation(targetRule);
SpreadsheetApp.flush();
ui.alert("Import successful!");
return;
}