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

Time:10-21

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();
}

CodePudding user response:

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');

  if( sheet.getLastRow() > 0 ) csvData.shift(); // remove the header
  sheet.getRange(sheet.getLastRow() 1,1,csvData.length,csvData[0].length).setValues(csvData);
}
  • Related