javascriptgoogle-sheetsgoogle-apps-scriptweb-applications

Processing multiple .csv files

I've been working on a process of batch importing the data from multiple .csv files on my computer into Google Sheets without uploading all of them to Google Drive first.

I currently have the following:

index.html

<form>
  <input type="file" name="file" onchange="importCsv(this.parentNode)" accept=".csv,text/csv" multiple>
</form>
<p id="progress" style="display:none;">
  <label for="upload" style="font-family: sans-serif;">Uploadling...</label>
  <progress id="upload"></progress>
</p>
<script>
  function readFileAsBuffer(file){
    return new Promise(function(resolve,reject){
      let fr = new FileReader();

      fr.onload = function(){
          resolve(fr.result);
      };

      fr.onerror = function(){
          reject(fr);
      };

      fr.readAsArrayBuffer(file);
    });
  }
  async function importCsv(e) {
    document.getElementById("progress").style.display = "block";
    const files = e.file.files;
    let fileReaders = [];
    let fileValues = [];

    for(let i = 0;i < files.length;i++){ 
      fileReaders.push(readFileAsBuffer(files[i])); 
    }

    await Promise.all(fileReaders).then((values) => {
      fileValues.push(values);

    });

    for(let i=0; i < files.length; i++)
    {
      let file = files[i];
      await google.script.run.withSuccessHandler(google.script.host.close).importCsv([[...new Int8Array(fileValues[0][i])], file.type, file.name]);
    }    
  }
</script>

script.gs

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Import").addItem("Import CSVs", "importCsv").addToUi();
}

function importCsv(e){
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "Import CSVs");
    return;
  }
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 3, 1, csv.length, csv[0].length).setValues(csv);
  lock.releaseLock();
}

I initially had it set up not using promises, but I was finding that all of the files I tried to import would get placed into the spreadsheet out of order. I thought that by using promises to wait until each file was read and processed before passing over to the spreadsheet would help with this, but I'm still running into the same issue. Other than using the LockService object like I'm doing, does anyone have any ideas on how to make the script wait until each section is finished getting added to the spreadsheet?

Solution

  • When I saw your script for index.html, ' it seemed that the values of fileValues` included all data from the files. I thought that this might be able to be used. So, how about the following modification?

    HTML & Javascript: index.html

    <form>
  <input type="file" name="file" onchange="importCsv(this.parentNode)" accept=".csv,text/csv" multiple>
</form>
<p id="progress" style="display:none;">
  <label for="upload" style="font-family: sans-serif;">Uploadling...</label>
  <progress id="upload"></progress>
</p>
<script>
function readFileAsBuffer(file) {
  return new Promise(function (resolve, reject) {
    let fr = new FileReader();

    fr.onload = function () {
      resolve([[...new Int8Array(fr.result)], file.type, file.name]);
    };

    fr.onerror = function () {
      reject(fr);
    };

    fr.readAsArrayBuffer(file);
  });
}
async function importCsv(e) {
  document.getElementById("progress").style.display = "block";
  const files = e.file.files;
  let fileReaders = [];
  let fileValues = [];

  for (let i = 0; i < files.length; i++) {
    fileReaders.push(readFileAsBuffer(files[i]));
  }

  await Promise.all(fileReaders).then((values) => {
    fileValues.push(...values);

  });
  google.script.run.withSuccessHandler(google.script.host.close).importCsv(fileValues);
}
</script>

    Google Apps Script: script.gs

    function importCsv(e) {
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "Import CSVs");
    return;
  }

  const lock = LockService.getDocumentLock();
  if (lock.tryLock(350000)) {
    try {

      const array = e.flatMap(v => [...Utilities.parseCsv(Utilities.newBlob(...v).getDataAsString()), ...Array(2).fill([])]);
      const maxLen = Math.max(...array.map((r) => r.length));
      const values = array.map((r) => [...r, ...Array(maxLen - r.length).fill(null)]);
      const sheet = SpreadsheetApp.getActiveSheet();
      sheet.getRange(sheet.getLastRow() + 3, 1, values.length, values[0].length).setValues(values);


    } catch (err) {
      return ContentService.createTextOutput(err.message);
    } finally {
      lock.releaseLock();
      return ContentService.createTextOutput("Done");
    }
  } else {
    return ContentService.createTextOutput("Timeout");
  }
}

    In this modification, fileValues includes all data from the files. fileValues is sent to Google Apps Script, and the file contents are converted to an array. Then, the array is put into the active sheet. In your showing script, I guessed that you might want to insert 2 empty rows between each file. So, this modified script adds 2 empty rows to every file.

    Note: