Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

unable to get a do-while loop working inside an if-else statement using google appscript

I’m trying to achieve the following:

  1. Get appscript to recognise the latest csv file within a folder in google drive.
  2. Once it does recognise it, it will import the file into google sheets.
  3. If a file has been previously uploaded to the sheet, appscript should upload the new lines below the currently existing lines.
  4. remove the headers (i.e Row 1) of any subsequent file uploaded after the first one.

The code I wrote keeps going in a loop and I can’t seem to figure out to progress further.
I’ve tried a for loop inside a if-else statement but that terminates after the first iteration, hence attempting a do-while loop.

    function myFunction() {
  function importData()
  {
       function getLatestFileId(){

   var folder = DriveApp.getFoldersByName("Printer CSVs");
   var files = folder.next().getFiles();
   var lastFileId = files.next().getId();


   return lastFileId.toString();

}
    var ss = SpreadsheetApp.getActive();
    var id = getLatestFileId();
    var lastworkingrow = ss.getSheetByName('Sheet1').getLastRow();
    console.log(typeof lastworkingrow);
    files = DriveApp.getFileById(id);
    
    var csvData = Utilities.parseCsv(files.getBlob().getDataAsString());
    console.log(typeof csvData.length);
    var sheet = ss.getSheetByName('Sheet1');
    var i=0;

   if (lastworkingrow < csvData.length) {
     var i=0;
        do {
      sheet.getRange(i+1, 1, 1, (csvData[i].length)).setValues(new Array(csvData[i]));  
    i++;
} while (i < (csvData.length));
    } else {
    var i=0;
    do {
      var csvData = Utilities.parseCsv(files.getBlob().getDataAsString());
      sheet.getRange(i+1, 1, 1, (csvData[i].length)).setValues(new Array(csvData[i]));  
    i++;
} while (i < (lastworkingrow + csvData.length));
  }
  }
  importData();
}

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Why not simply do this.

function importData() {
  function getLatestFileId() {
    let folder = DriveApp.getFoldersByName("Printer CSVs");
    let files = folder.next().getFiles();
    let lastFileId = files.next().getId();  // this will be the first
    return lastFileId;
  }
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let id = getLatestFileId();
  let files = DriveApp.getFileById(id);
    
  let csvData = Utilities.parseCsv(files.getBlob().getDataAsString());
  console.log(typeof csvData.length);
  let sheet = ss.getSheetByName('Sheet1');

  csvData.shift(); // remove the header
  sheet.getRange(sheet.getLastRow()+1,1,csvData.length,csvData[0].length).setValues(csvData);
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading