Question

0

154

Visualizações

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

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

about 4 years ago · Juan Pablo Isaza

1 Respostas

Responde à pergunta

0

Responde à pergunta

Encontrar trabalhos remotos

Answer 1 · 2022-07-10T10:05:42.763Z

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.

0

154