I am trying to store CSV data to a worksheet via a macro. Data comes from 30+ distinct files, it is inconvenient to import each file via the menu. However, writing pieces of data to the worksheet takes several minutes with SetValue(). Is there a better way to load data in the macro?
Interestingly, if data is randomly generated, SetValue() is fast enough. Here is an example macro to illustrate the problem:
(async function()
{
function generateData(nbRows) {
let data = new Array(nbRows);
for (let i = 0; i < nbRows; i++) {
data[i] = [
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random(),
Math.random()
];
}
return data;
}
async function fetchData(nbRows) {
let url = 'https://tb1-ai.pages.emse.fr/edc1/20131113-YE.txt';
let response = await fetch(url);
let txt = await response.text();
let data = txt
.split('\n')
.map(row => row.split('\t'));
return data.slice(0, nbRows);
}
let sheet = Api.GetActiveSheet();
for (let nbRows = 100; nbRows < 1e3; nbRows += 100) {
let range = sheet.GetRange(`A1:M${nbRows}`);
// uncomment either line
//let data = generateData(nbRows);
let data = await fetchData(nbRows);
// note: fetchData() takes ~100ms
let t1 = Date.now();
range.SetValue(data);
let t2 = Date.now();
console.log(`${nbRows} rows inserted in ${t2 - t1} ms`);
// output: 100 rows inserted in 2620 ms
// output: 200 rows inserted in 5323 ms
// output: 300 rows inserted in 17608 ms
// ...
}
})();