Improve Code of automatically import data from uploaded CSV or XLS file into Google Sheets

Question

The problem is: whenever I run the script, it creates a new sheet named "NEWDATA", so that I cant make a trigger to automatically run the script, (it meant to I have to delete NEWDATA sheet to run script one more than time).

if ( fi.hasNext() ) { // proceed if "report.csv" file exists in the reports folder
var file = fi.next();
var csv = file.getBlob().getDataAsString();
var csvData = CSVToArray(csv); // see below for CSVToArray function
var newsheet = ss.insertSheet('NEWDATA');// create a 'NEWDATA' sheet to store imported data
// loop through csv data array and insert (append) as rows into 'NEWDATA' sheet
for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
  newsheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
}
/*
** report data is now in 'NEWDATA' sheet in the spreadsheet - process it as needed,
** then delete 'NEWDATA' sheet using ss.deleteSheet(newsheet)
*/
// rename the report.csv file so it is not processed on next scheduled run
file.setName("report-"+(new Date().toString())+".csv");

I expect someone can help me fix this code to the result: it writes data from csv file into an existing sheet, so data will be continuable, I meant to "appendrow" function. Sorry I'm new with this, and I tried many ways, but it too hard for me, thank you.

Amit Agarwal Amit Agarwal · Accepted Answer · 2019-07-21T05:50:17

Instead of:

var newsheet = ss.insertSheet('NEWDATA');

Use:

var newsheet = ss.getSheetByName('NEWDATA') || ss.insertSheet('NEWDATA');

It will check if a sheet exists, before creating a new one.

Improve Code of automatically import data from uploaded CSV or XLS file into Google Sheets

1 Answers