Importing multiple CSV files to google sheets with

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

Answer 1

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