Script misbehaves
Ashish Srivastava
Fabrice Faucheux
Okay, let's break down the issues with the script and how to fix it to meet your requirements.
Understanding the Problems
- Overwriting Destination Data (Columns E onwards): The main issue is the syncSortingEffect function. It gets all columns from the sorted source sheet (sourceSheet.getLastColumn()) and copies them to the destination sheet, completely overwriting anything that was previously in columns E and beyond in the destination.
- Syncing Logic: The current sync clears the entire destination data range (all columns from startRow down) and then pastes the entire source data range. You only want to update columns A to D in the destination, leaving E onwards untouched, and ensuring the rows match the source sheet's sorted order.
- Trigger: The function name menuTriggeredSort implies it runs manually from a menu. Your requirement "when new data added" suggests you want this to run automatically (e.g., using an onEdit or onChange trigger). Running a full sort and sync on every edit can be inefficient and disruptive. A manual trigger (like the one you have) or a more specific automatic trigger might be better. We'll fix the core logic first, assuming the function is called somehow.
Corrected Script
Here's the modified script that addresses the core problem of syncing only columns A-D while preserving the row order from the source sort:
/**
* Sorts the Source sheet (rows 5+) by Column B then C,
* and updates columns A-D in the Destination sheet(s) to match the sorted order,
* leaving columns E onwards in the destination sheets untouched.
*
* Designed to be run manually from a menu or potentially via a trigger.
*/
function menuTriggeredSortAndSyncSpecificColumns() {
var sourceSheetId = "sssssssssssssssssssssss"; // <--- Replace with your ACTUAL Source spreadsheet ID
var sourceSheetName = "Source"; // Source sheet name
var destSheetIds = [
"ddddddddddddddddddddddddddddddd", // <--- Replace with your ACTUAL Destination spreadsheet ID(s)
// Add more destination sheet IDs here if needed
];
var destSheetName = "Sheet1"; // Destination sheet name (must be the same in all destination spreadsheets)
var startRow = 5; // Start row for sorting and syncing
var numColsToSync = 4; // Sync columns A, B, C, D (1 to 4)
try {
var sourceSpreadsheet = SpreadsheetApp.openById(sourceSheetId);
var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
if (!sourceSheet) {
SpreadsheetApp.getUi().alert("Source sheet '" + sourceSheetName + "' not found in spreadsheet ID: " + sourceSheetId);
return;
}
// Get the full data range starting from startRow in the source sheet
var sourceLastRow = sourceSheet.getLastRow();
if (sourceLastRow < startRow) {
Logger.log("No data to sort or sync starting from row " + startRow + " in the source sheet.");
// Optional: You might want to clear A:D in destination sheets if source is empty
// clearDestinationColumns(destSheetIds, destSheetName, startRow, numColsToSync);
SpreadsheetApp.getUi().alert("No data found in the source sheet starting from row " + startRow + ".");
return;
}
var sortRange = sourceSheet.getRange(startRow, 1, sourceLastRow - startRow + 1, sourceSheet.getLastColumn());
// --- Optional Protection ---
// If column C contains formulas or requires strict protection during the sort, keep this.
// Otherwise, you can remove the protection block for simplicity.
var rangeToProtect = sourceSheet.getRange("C:C"); // Protect Column C
var protection = rangeToProtect.protect();
protection.setWarningOnly(false); // Prevent editing during script execution
Logger.log("Applied temporary protection to Column C.");
// --- End Optional Protection ---
try {
// Sort the source sheet by column B (2) then by column C (3)
Logger.log("Sorting source sheet range: " + sortRange.getA1Notation());
sortRange.sort([{ column: 2, ascending: true }, { column: 3, ascending: true }]);
SpreadsheetApp.flush(); // Ensure the sort is applied before proceeding
Logger.log("Source sheet sorted.");
// Get the newly sorted data from Columns A-D only
var sourceDataColsAD = sourceSheet.getRange(startRow, 1, sourceLastRow - startRow + 1, numColsToSync).getValues();
Logger.log("Fetched sorted data for columns A-D from source.");
// Apply the sorting effect (sync Columns A-D) to destination sheets
destSheetIds.forEach(function (destSheetId) {
try {
var destSpreadsheet = SpreadsheetApp.openById(destSheetId);
var destSheet = destSpreadsheet.getSheetByName(destSheetName);
if (destSheet) {
Logger.log("Syncing columns A-D to destination: " + destSheetId + " - " + destSheetName);
syncSpecificColumns(sourceDataColsAD, destSheet, startRow, numColsToSync);
} else {
Logger.log("Destination sheet '" + destSheetName + "' not found in spreadsheet ID: " + destSheetId);
}
} catch (error) {
Logger.log("Error accessing or syncing destination spreadsheet " + destSheetId + ": " + error.message);
}
});
SpreadsheetApp.getUi().alert("Sorting and syncing of columns A-D complete!");
} catch (error) {
Logger.log("Error during sorting or data retrieval: " + error.message);
SpreadsheetApp.getUi().alert("An error occurred during sorting: " + error.message);
} finally {
// --- Optional Protection Removal ---
// Ensure column C protection is removed even if errors occur
try {
protection.remove();
Logger.log("Removed temporary protection from Column C.");
} catch (unlockError) {
// Log error if protection was already removed or another issue happened
Logger.log("Info: Could not remove protection (may have already been removed or error occurred): " + unlockError.message);
}
// --- End Optional Protection Removal ---
}
} catch (error) {
Logger.log("Error opening source spreadsheet " + sourceSheetId + ": " + error.message);
SpreadsheetApp.getUi().alert("An error occurred accessing the source spreadsheet: " + error.message);
}
}
/**
* Clears and updates specific columns (A-D) in the destination sheet
* based on the provided sorted data, matching row for row.
*/
function syncSpecificColumns(sourceDataColsAD, destSheet, startRow, numColsToSync) {
var numRows = sourceDataColsAD.length;
var destLastRow = destSheet.getLastRow();
// Define the range in the destination sheet to update (Columns A-D)
var destRangeColsAD = destSheet.getRange(startRow, 1, numRows, numColsToSync);
// Clear existing content only in Columns A-D for the affected rows
Logger.log("Clearing destination range: " + destRangeColsAD.getA1Notation());
destRangeColsAD.clearContent(); // Use clearContent to preserve formatting
// Write the new sorted data (A-D) from the source to the destination
Logger.log("Setting values in destination range: " + destRangeColsAD.getA1Notation());
destRangeColsAD.setValues(sourceDataColsAD);
// Optional: Clear columns A-D in destination rows that *no longer exist* in the source
// This happens if rows were deleted from the source before sorting.
var sourceEndOfDataRow = startRow + numRows - 1;
if (destLastRow > sourceEndOfDataRow && destLastRow >= startRow) {
var clearRange = destSheet.getRange(sourceEndOfDataRow + 1, 1, destLastRow - sourceEndOfDataRow, numColsToSync);
Logger.log("Clearing orphaned rows A-D in destination: " + clearRange.getA1Notation());
clearRange.clearContent();
}
SpreadsheetApp.flush(); // Ensure changes are written to the destination sheet
}
// --- Helper function if needed for the case where source becomes empty ---
/*
function clearDestinationColumns(destSheetIds, destSheetName, startRow, numColsToSync) {
destSheetIds.forEach(function(destSheetId) {
try {
var destSpreadsheet = SpreadsheetApp.openById(destSheetId);
var destSheet = destSpreadsheet.getSheetByName(destSheetName);
if (destSheet) {
var destLastRow = destSheet.getLastRow();
if (destLastRow >= startRow) {
destSheet.getRange(startRow, 1, destLastRow - startRow + 1, numColsToSync).clearContent();
Logger.log(`Cleared columns A-D in destination ${destSheetId} - ${destSheetName} from row ${startRow}`);
}
}
} catch(e) {
Logger.log(`Error clearing columns A-D in destination ${destSheetId}: ${e}`);
}
});
}
*/
Explanation of Changes:
- New Function syncSpecificColumns: This replaces the old syncSortingEffect.
- It takes the already sorted sourceDataColsAD (which now only contains columns A-D from the source) as input.
- It calculates the corresponding range in the destination sheet (destRangeColsAD), but only for columns A-D (numColsToSync = 4).
- It uses destRangeColsAD.clearContent() to clear only the content within columns A-D of the relevant rows in the destination sheet, preserving formatting and leaving columns E onwards untouched.
- It then uses destRangeColsAD.setValues(sourceDataColsAD) to paste the sorted A-D data from the source into the destination's A-D columns.
- It includes logic to clear A-D in any "orphaned" rows in the destination (rows that exist in the destination below the newly sorted source data, perhaps from previous runs where the source had more data).
- Main Function Modifications:
- Renamed to menuTriggeredSortAndSyncSpecificColumns for clarity.
- Added numColsToSync = 4 variable.
- After sorting the sourceSheet, it now specifically gets only columns A-D using sourceSheet.getRange(startRow, 1, sourceLastRow - startRow + 1, numColsToSync).getValues();.
- The loop now calls the new syncSpecificColumns function, passing the sourceDataColsAD data.
- Added SpreadsheetApp.flush() calls to help ensure operations complete in order.
- Added more Logger.log statements to help with debugging if needed (View logs via Apps Script editor -> Executions).
- Improved error messages and handling for sheets not found.
- Commented the optional Column C protection block – remove it if you don't need it.
How to Use:
- Replace IDs: Make sure you replace "sssssssssssssssssssssss" and "ddddddddddddddddddddddddddddddd" with the actual IDs of your Source and Destination spreadsheets.
- Replace Script: Open the script editor for your Source spreadsheet (Tools > Script editor) and replace your entire old script with this new corrected script.
- Save: Save the script project.
- Run Manually: Run the function menuTriggeredSortAndSyncSpecificColumns either directly from the script editor or by setting up a custom menu item if you had one before. It will sort the source and update A-D in the destination(s).
Regarding Automatic Trigger ("when new data added"):
- You could set up an onEdit(e) or onChange(e) trigger (Project Triggers page in the Apps Script editor).
- Caution: Running a full sort and sync on every single edit can be very slow, hit execution time limits, and might be disruptive if people are actively editing.
- Recommendation:
- Stick with the manual trigger (running it when needed).
- Or, modify the script further to run only under specific conditions (e.g., when a value is added to a specific column in a new row, or only when a specific cell is changed to signal "run the sort"). This requires more complex trigger logic using the event object e.
This corrected script directly addresses the requirement to only sync columns A-D while maintaining the row order determined by the source sheet sort. Remember to replace the placeholder IDs!