Using Google Apps Script to copy .CSV file data to Google Sheets

Question

I am needing to loop through each .CSV file in a source folder and copy the data from each file into a new sheet/tab in one Google Sheets file. I've found a couple of answers related to this topic but I'm getting errors with both code sets. Here's the code I'm attempting to build a solution from:

function appendCSV() {  
    var file = DriveApp.getFilesByName("myCSVFile.csv").next(); 
    var csvData = Utilities.parseCsv(file.getBlob().getDataAsString()); 
    var sheet = SpreadsheetApp.getActiveSheet(); 
    var lastRow=sheet.getLastRow(); 
    sheet.getRange(lastRow, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Here's the first error I'm getting:

Cannot retrieve the next object: iterator has reached the end. (line 2, file "Code")

I can delete the .next() method from line 2 and then I get another error:

TypeError: Cannot find function getBlob in object FileIterator. (line 3, file "Code")

Can anyone identify what might be going wrong here?

Cooper · Accepted Answer · 2020-02-01 21:22:43Z

4

You need to provide Spreadsheet Id and folder id. Also you may wish to change the sheet names of the created sheets/tabs.

function loadCSVFilesIntoSheets() { 
  var ss=SpreadsheetApp.openById('SpreadsheetId')
  var folder=DriveApp.getFolderById('folderId');
  var files=folder.getFilesByType(MimeType.CSV);
  while(files.hasNext()) {
    var file=files.next();
    var vA=Utilities.parseCsv(file.getBlob().getDataAsString());
    var ts=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yy HH:mm:ss")
    var sh=ss.insertSheet(file.getName()+'-'+ts);
    sh.getRange(1,1,vA.length,vA[0].length).setValues(vA);
  }
}

Getting Spreadsheet Id: https://docs.google.com/spreadsheets/d/SpreadsheetIdHere/edit#gid=1655026329

Folder Id: https://drive.google.com/drive/folders/folder id here

DriveApp

Utilities.parseCsv()

Utilities.formatDate()

edited Feb 1, 2020 at 21:22

answered Feb 1, 2020 at 20:36

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mad Lamb Over a year ago

Thank you for the response!! Much appreciated. I inserted a spreadsheet ID and folder ID as you suggested. When I run the code, I get this error: "Missing ; before statement. (line 10, file "Code")" which is the last line of code.

Cooper Over a year ago

Sorry I fixed it. Last minute change.

Mad Lamb Over a year ago

That works! Thank you so much! Another question: How can I open both the folder and file by name instead of by ID. I tried open() instead of the openByID(), but that didn't seem to work - says it 'cannot find method open()'. Thanks again for your help!

Cooper Over a year ago

Id's are more meaningfull in Google Drive. Infact, it's not unusual to find files with the same name within the same directory. The only command in DriveApp to get the files by name is Folder.getFilesByName() which return an iterator for the set. If you know for an absolute fact that it's the only file with that name then you can get it with `var file=Folder.getFilesByName().next();

Wicket · Accepted Answer · 2020-02-01 22:31:28Z

1

The first error that you are getting

Cannot retrieve the next object: iterator has reached the end. (line 2, file "Code")

occurs because apparently there isn't any file named myCSVFile.csv

var file = DriveApp.getFilesByName("myCSVFile.csv").next();

The second error occurs for the same reason.

First, you should double check that the filename is correct and that this file is owned or shared with the same account used to run the script.

answered Feb 1, 2020 at 22:31

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Collectives™ on Stack Overflow

Using Google Apps Script to copy .CSV file data to Google Sheets

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related