I'm trying to achieve the following:
- Get appscript to recognise the latest csv file within a folder in google drive.
- Once it does recognise it, it will import the file into google sheets.
- If a file has been previously uploaded to the sheet, appscript should upload the new lines below the currently existing lines.
- 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);
}