Get specific rows from CSV file to googlesheet using Google Apps Script

Question

I have a CSV file uploaded to a Google Drive folder. Its column A has different names of people. Each name has its own google sheet. I need each name(their data) to be imported to their corresponding google sheet.

If the source data is a google sheet, I can do it like this(see below). But, I have zero idea how to do it if my source data is a csv file:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Testname");
  var lr = ss.getLastRow();
  
  for (i=2; i<=lr; i++) {
    var conditionRange = ss.getRange(i,1).getValue();
      if (conditionRange == "John") {
        // All rows with John name will be imported to John's googlesheet

Iamblichus · Accepted Answer · 2021-11-15 10:04:51Z

1

Solution:

Use Utilities.parseCsv to retrieve the row data from your csv. With this, you can retrieve a 2D array with the csv data, which you can then loop through.

Code snippet:

function myFunction() {
  const csvFileId = "YOUR_CSV_FILE_ID";
  const csv = DriveApp.getFileById(csvFileId);
  const csvBlob = csv.getBlob();
  const csvData = Utilities.parseCsv(csvBlob.getDataAsString());
  csvData.shift(); // Remove headers
  for (let i = 0; i < csvData.length; i++) {
    if (csvData[i][0] == "John") {
      // All rows with John name will be imported to John's googlesheet
    }
  }
}

Note:

The loop process could probably be made more efficient, but without knowing what exactly you are doing in each iteration, I prefer leaving it as it is.
I assumed that you know the CSV file id, so it can be retrieved via DriveApp.getFileById. If that's not the case, please clarify what you do know about this file (name, parents, etc.) and I will modify the snippet accordingly.

answered Nov 15, 2021 at 10:04

Iamblichus

19.5k2 gold badges14 silver badges27 bronze badges

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

4 Comments

cgstats Over a year ago

Thanks for the info. Yes, I know the ID of the CSV file and Folder ID. But, now I'm getting this error(Exception: Could not parse text) on line 13. The script below is the line 13th: var csvData = Utilities.parseCsv(fileBlob.getDataAsString());

Iamblichus Over a year ago

@cgstats I cannot reproduce this. This is most likely related to the specific data in your csv. Therefore, in order to debug this, can you provide an example csv which can be used to reproduce this? (could be related to this, for example).

Iamblichus Over a year ago

@cgstats Hi, was your issue solved?

cgstats Over a year ago

Thanks for checking in. I haven't tried fixing it again caused by pile of work. I will update here. Sorry.

Collectives™ on Stack Overflow

Get specific rows from CSV file to googlesheet using Google Apps Script

1 Answer 1

Solution:

Code snippet:

Note:

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solution:

Code snippet:

Note:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related