Google script getRange Set Values doesn't always match-CodePudding

So i'm trying to import a csv from API and put it into spreadsheet however when i'm writing the values in range, i keep getting that column is 15 instead of 14 and when i make it 15 it gives range is 14.

something is going off but there are no extra headers. i need a proper stable fix for it please

function importCSVFromAPI() {

  var response = UrlFetchApp.fetch("https://www.gw2spidy.com/api/v0.9/csv/all-items/all");
  // Is the attachment a CSV file
/*   var file = Utilities.parseCsv(response.getDataAsString(), ","); */
  var data = response.getBlob().getDataAsString();
  data.replace("\"","");
  var lines = data.split("\n");
  var data = lines.map(function(lin){return lin.split(",")});
  var dest = SpreadsheetApp.getActiveSheet();
  dest.clear();
  Logger.log(data[0].length);
  dest.getRange(1, 1, data.length, data[0].length).setValues(data);

  }

CodePudding user response：

I thought that the CSV data from the URL might be large. In this case, how about using Sheets API? When the Sheets API is used, the process cost for putting the CSV data to Spreadsheet can be reduced. And also, the CSV data is automatically parsed. By this, I thought that your issue might be removed. When this is reflected in your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function importCSVFromAPI() {
  var response = UrlFetchApp.fetch("https://www.gw2spidy.com/api/v0.9/csv/all-items/all");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dest = ss.getActiveSheet();
  dest.clear();
  SpreadsheetApp.flush();
  var req = { pasteData: { data: response.getContentText(), delimiter: ",", coordinate: { sheetId: dest.getSheetId() } } };
  Sheets.Spreadsheets.batchUpdate({requests: [req]}, ss.getId());
}

When you run this script, the retrieved CSV data is put to the active sheet.

References:

CodePudding user response：

Your data has commas in the titles

23791,"Memoirs of Captain Greywind**,** Unabridged"

So splitting by commas creates an extra column for that row. You need to sanitize the tittles before splitting or before assigning it to the range.