Importing multiple CSV files to google sheets with Google Script

Question

I am working on a google script to import CSV files from web urls to individual sheets in a google sheet. I would like to have it clear the contents of certain columns before importing the new info. I had had trouble getting the script to clear contents prior to importing. I have also had in include part of one CSV along with another on a sheet. I think I need to add something to the script between CSV files. I also need something added to clear contents prior to importing. I don't want to just clear the sheet because there are formulas that need to remain. I also don't want to use the Google IMPORTDATA function because it is unreliable.

Here is my current script (URLs removed):

function importCSVFromWeb() {

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var ss = SpreadsheetApp.openByUrl('spreadsheeturlhere');
  var sheet = SpreadsheetApp.getActiveSheet();
  var ss = sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheet = SpreadsheetApp.getActive().getSheetByName('RentPaid');
  var ss = sheet.getRange(3, 3, csvData.length, csvData[0].length).setValues(csvData);

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheet = SpreadsheetApp.getActive().getSheetByName('Tenants');
  var ss = sheet.getRange(1, 2, csvData.length, csvData[0].length).setValues(csvData);

  var csvUrl = "http://csvurlhere";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var sheet = SpreadsheetApp.getActive().getSheetByName('Owners');
  var ss = sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

Dean Ransevycz · Accepted Answer · 2017-10-23 05:24:25Z

2

To clear the contents of a sheet or a range of cells within a sheet, you can use sheet.clearContents() or range.clearContent(), which will remove only values & leave your formatting & formulas in tact.

I note that your line var ss = SpreadsheetApp.openByUrl('spreadsheeturlhere'); does nothing as you subsequently redeclare the ss variable. If the script is bound to a spreadsheet, you don't need this line as calls to getActive() & getActiveSheet() will give you a reference to the host spreadsheet. Your calls to SpreadsheetApp.getActive().getSheetByName() are returning references to the host spreadsheet anyway, not the one you open by URL.

I would also consider changing your function to accept the CSV source URLs, sheet names & cell anchors as parameters so that the code is easier to maintain. e.g.

function importCSVFromWeb(csvUrl, sheetName, dataAnchor) {
  /* csvUrl:     the CSV source URL
     sheetName:  the name of the sheet to add the data
     dataAnchor: the cell origin for the range.setValues() call as an array 
                 i.e. dataAnchor = [row, col]
  */
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  var ss = SpreadsheetApp.getActive();
  // note that you'll need to pass in a name for each sheet, 
  //   including the first one where you used SpreadsheetApp().getActiveSheet() previously
  var sheet = ss.getSheetByName(sheetName);
  sheet.getRange(dataAnchor[0], dataAnchor[1], csvData.length, csvData[0].length).setValues(csvData);
}

function feedCSVImport(){
  var urls_sheets_list = [["csv_source_url_1", "Sheet1", [1,1]],
                          ["csv_source_url_2", "RentPaid", [3,3]],
                          ["csv_source_url_3", "Tenants", [1,2]],
                          ["csv_source_url_4", "Owners", [1,1]]
                         ];
  for(var i = 0, lim = url_sheets_list.length; i < lim; ++i){
    importCSVFromWeb(url_sheets_list[i][0], url_sheets_list[i][1], url_sheets_list[i][2]);
  }
}

edited Oct 23, 2017 at 5:24

answered Oct 23, 2017 at 3:23

Dean Ransevycz

9539 silver badges23 bronze badges

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

4 Comments

Rob Over a year ago

Thank you. I am implementing these changes and am getting the following error: "TypeError: Cannot read property "0" from undefined. (line 23, file "Code")" Line 23 is: importCSVFromWeb(url_sheets_list.length[i][0], url_sheets_list.length[i][1], url_sheets_list.length[i][2]);

Dean Ransevycz Over a year ago

Sorry, @Rob. I've fixed the typos in the example. It's not pretty, but it was intended to demonstrate a way of breaking down your problem.

Rob Over a year ago

This is working great, thanks for the help @Dean Ransevycz. If I wanted to incorporate the range.clearContent(), how do I add that to the parameters so it clears the range before entering the new content?

Dean Ransevycz Over a year ago

If you want to clear data from all of the sheets, then you don't need to add a new parameter. I'd only consider adding a boolean parameter if you want to flag specific sheets to be cleared before pasting the data -- you could call the clearContent() method based on that. Have a look at the sheet class (developers.google.com/apps-script/reference/spreadsheet/sheet) as well, specifically the getLastRow() & getLastColumn() methods. Have a go at solving the problem & ask another question if you get stuck.

Collectives™ on Stack Overflow

Importing multiple CSV files to google sheets with Google Script

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related