This content originally appeared on DEV Community and was authored by Hafiz
I am currently using a script that copies both the data and formatting of cells. While the code functions correctly, it is not performing efficiently. Even after incorporating a condition based on the last used row in Column C, the performance issues persist.
The process takes approximately 10 minutes to copy around 3,500 rows from five folders. Although I recognize my experience with this programming language is limited.
I would greatly appreciate any assistance from experts in identifying and resolving this issue.
Thank you.
`function copyDataWithColorsToMasterSheet() {
var folderIds = ['', '', '', '', ''];
var masterSheetId = '';
var masterSpreadsheet = SpreadsheetApp.openById(masterSheetId);
var masterSheet = masterSpreadsheet.getActiveSheet();
masterSheet.clear(); // Clear the master sheet to avoid duplicate data
folderIds.forEach(function(folderId) {
processFolder(folderId, masterSheet);
});
}
function processFolder(folderId, masterSheet) {
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
var batchData = [];
var batchBackgrounds = [];
while (files.hasNext()) {
var file = files.next();
processSheet(file, batchData, batchBackgrounds);
}
if (batchData.length > 0) {
var masterLastRow = masterSheet.getLastRow() + 1;
var targetRange = masterSheet.getRange(masterLastRow, 1, batchData.length, batchData[0].length);
targetRange.setValues(batchData);
targetRange.setBackgrounds(batchBackgrounds);
}
var subfolders = folder.getFolders();
while (subfolders.hasNext()) {
var subfolder = subfolders.next();
processFolder(subfolder.getId(), masterSheet);
}
}
function processSheet(file, batchData, batchBackgrounds) {
var spreadsheet = SpreadsheetApp.open(file);
var sheet = spreadsheet.getSheets()[0];
var lastUsedRowInColumnC = getLastUsedRowInColumnC(sheet);
if (lastUsedRowInColumnC < 2) return;
var lastColumn = sheet.getLastColumn();
var dataRange = sheet.getRange(2, 1, lastUsedRowInColumnC - 1, lastColumn);
var data = dataRange.getValues();
var backgrounds = dataRange.getBackgrounds();
// Append data and backgrounds to batch arrays
batchData.push.apply(batchData, data);
batchBackgrounds.push.apply(batchBackgrounds, backgrounds);
}
function getLastUsedRowInColumnC(sheet) {
var columnC = sheet.getRange("C:C").getValues();
for (var i = columnC.length - 1; i >= 0; i--) {
if (columnC[i][0] !== "") {
return i + 1; // Return the row number (1-based index)
}
}
return 1; // Default to 1 if no data is found
}`
This content originally appeared on DEV Community and was authored by Hafiz
Hafiz | Sciencx (2024-08-20T22:43:46+00:00) Looking for a way to optimize my Javascript code. Retrieved from https://www.scien.cx/2024/08/20/looking-for-a-way-to-optimize-my-javascript-code/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.