Google-sheets – Why doesn’t this script populate sheet after parsing data from CSV file

csvgoogle sheetsgoogle-apps-script

I got the below script which seems to work from time to time. It runs with no bugs reported, but it doesn't populate the sheet it is supposed to:

function importCSVFromWeb(url) {

  // Provide the full URL of the CSV file.
  var csvUrl = "Https...";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  var csvData = Utilities.parseCsv(csvContent);

  var sheet = SpreadsheetApp.getActiveSpreadSheet().getSheetByName('Entradas');
   if(sheet.getName()=='Entradas'){
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
   }
}

Best Answer

It rather depends on what the website specified by that URL returns. If it's down, disallows robots, or returns data in a format that's not expected by parseCsv, then something is going to break. Try inserting debugging messages, such as

function importCSVFromWeb(url) {

  // Provide the full URL of the CSV file.
  var csvUrl = "Https...";
  var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
  Logger.log(csvContent); // log for debugging, if used interactively
  var csvData = Utilities.parseCsv(csvContent);
  if (!csvData || !(csvData.length)) {
      MailApp.sendEmail('you', 'error report', 'could not parse data ' + csvContent); 
  }   // emailing bug report  

  var sheet = SpreadsheetApp.getActiveSpreadSheet().getSheetByName('Entradas');
  sheet.getRange(1, 1, csvData.length, csvData[0].length); 
}

The check if(sheet.getName()=='Entradas') was unnecessary since the sheet was obtained by that name.

Related Solutions

Google Sheets – Pull CSV Data from URL

Drop this formula in the first cell of your google spreadsheet:

=importData("http://example.com/activities.csv")

And it will automatically fill out the rest of the current spreadsheet with as many columns and rows as it needs until all the data from the original source csv is displayed.

There are many other powerful ways to feed Google Spreadsheets from all kinds of external sources. Check out this article for some demonstrations.

Google Sheets – Using Triggers with Parameter-Requiring Functions

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}

Best Answer

Related Solutions

Google Sheets – Pull CSV Data from URL

Google Sheets – Using Triggers with Parameter-Requiring Functions

Related Topic