'Custom Sort apps script (pulling data from 12 sheets) is slow
I have a code that someone wrote for me to make import data easier instead of having to write a formula that pulls data from 12 sheets (and is open to error). The script has a sheet of "links" along with sheet names and ranges. It works fine when I use the "menu" that it created for importing the data, but it is slow (I'm guessing because it is going through 12 sheets). If i need to link the spreadsheet, please let me know.
function onOpen(e) {
SpreadsheetApp.getUi().createMenu('IMPORT')
.addItem('Update data', 'customImport')
.addToUi();
}
function customImport() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const inputSheet = ss.getSheetByName('Sheet16');
const outputSheet = ss.getSheetByName('Sheet17');
const columnNumberToFilter = 1
const inputValues = inputSheet.getRange(2, 1, inputSheet.getLastRow() - 1, 3).getValues();
const output = [];
inputValues.forEach(entrie => {
const [url, sheetname, range] = entrie;
const sheetRange = SpreadsheetApp.openByUrl(url)
.getSheetByName(sheetname)
.getRange(range)
const data = sheetRange.getValues();
data.forEach(row => {
if (row[columnNumberToFilter - 1] != "") {
output.push(row)
}
})
})
outputSheet.getRange(4,1,outputSheet.getLastRow(), outputSheet.getLastColumn()).clearContent();
outputSheet.getRange(4, 1, output.length, output[0].length).setValues(output).sort([4,5]
)
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
