CODEWITHSUNDEEP
google-apps-script
Jacky
Jacky

Reputation: 41

Inserting cell values into this Google app script code for importing is not working

I am writing a Google app script code so that csv data files can get imported to a Google spreadsheet automatically.

This is my code:

function myFunction() {
    var file = DriveApp.getFilesByName("data.csv").next();
    varcsvData = Utilities.parseCsv(file.getBlob().getDataAsString());
    var sheet = SpreadsheetApp.getActiveSheet();

    var numrows = csvData.length();
    var numcols = csvData[0].length();

    sheet.getRange(1,1, numrows, numcols).setValue(csvData)
}

The code works fine with when importing all rows and columns. But it does not work when I specify a cell range like 'B2:C5'. I tried inserting values for numrows (for cell range B2:C5 - rows 5 cols 2) and numcols. The code runs but there is no output to be seen. i.e. no data import on google sheet with the specified cell range.

Can someone please help?

Upvotes: 1

Views: 390

Answers (1)

Tanaike
Tanaike

Reputation: 201388

How about this modification?

Modification points:

  • Please modify length() to length.
  • Values retrieved by Utilities.parseCsv() is 2 dimensional array. In order to use this, please modify setValue(csvData) to setValues(csvData).

Modified script:

function myFunction() {
  var file = DriveApp.getFilesByName("data.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSheet();
  var numrows = csvData.length; // Modified
  var numcols = csvData[0].length; // Modified
  sheet.getRange(1,1, numrows, numcols).setValues(csvData); // Modified
}

Note:

  • In your script, the lengths for rows and columns are retrieved by numrows and numcols. So you can use them for the method of setValues().

References:

If I misunderstood your question, I apologize. And if this didn't work for your situation, can you provide a sample CSV file? By this, I would like to confirm it.

Edit:

  • You want to put the values by selecting the ranges from the CSV values of the file.

If my understanding is correct, how about this sample script?

Pattern 1:

In this pattern, the GridRange is used for selecting the values from the range of CSV data. Values selected using the GridRange are put to the Spreadsheet.

Sample script: 
function myFunction() {
  // As a sample, C3:D5 is as follows.
  var startRowIndex = 2;
  var endRowIndex = 5;
  var startColumnIndex = 2;
  var endColumnIndex = 4;

  var file = DriveApp.getFilesByName("data.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var values = [];
  for (var row = startRowIndex; row < endRowIndex; row++) {
    var temp = [];
    for (var col = startColumnIndex; col < endColumnIndex; col++) {
      temp.push(csvData[row][col]);
    }
    values.push(temp);
  }

  var sheet = SpreadsheetApp.getActiveSheet();
  var numrows = values.length;
  var numcols = values[0].length;
  sheet.getRange(1,1, numrows, numcols).setValues(values);
}

Pattern 2:

In this pattern, a1Notation is used for selecting the values from the range of CSV data. At first, all CSV data is put to Spreadsheet. Then, the values of the selected range are retrieved. The values are put to the Spreadsheet after the Spreadsheet is cleared.

Sample script: 
function myFunction() {
  var csvRange = "C3:D5"; // Please set the range of CSV data.

  var file = DriveApp.getFilesByName("data.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActiveSheet();
  var numrows = csvData.length;
  var numcols = csvData[0].length;
  sheet.getRange(1,1, numrows, numcols).setValues(csvData);

  var values = sheet.getRange(csvRange).getValues();
  sheet.clearContents();
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Upvotes: 1

Related Questions