I have a csv file that is overwritten with a new file each day.

The file contains a table with fields like: First Name | Last Name | Address etc.

The important aspect here is that the new CSV file will have new data but could be on any row. In fact, there will be rows that have the same person multiple times but with different data in some of the columns.

I have been trying to work out a way to compare the array that is coming into the spreadsheet from the CSV to the current data and then only copy new data when there is a row that doesn't match at all.

So far, I have sketched out this:

function copyData() {

  // get hold of the CSV File
  const fileName = 'Imported Data.csv';
  const file = DriveApp.getFilesByName(fileName).next();

  // get the data
  const dataBlob = file.getBlob();
  const csvString = dataBlob.getDataAsString();
  const csvData = Utilities.parseCsv(csvString);

  const sheet = SpreadsheetApp.getActiveSheet();
  const allData = sheet.getDataRange().getValues();

  for (let i = 0; i < allData.length; i  ) {

    const exists = csvData[i];

    // check to see if the file already exitst
    if (exists.hasNext) {

      console.log('yes');

    }
    else {

      console.log('no');
    }

  }

}

I realise that this is totally wrong but you can see that I am trying to see if the row in the array (csvData) is matched to the row in the spreadsheet (allData).

I am sure someone must have a better solution (that actually works) to add new data from the middle of a CSV file.

Sorry for my lack of knowledge on Apps Script.

CodePudding user response：

In your situation, how about the following modification?

Modified script:

function copyData() {
  const fileName = 'Imported Data.csv';
  const file = DriveApp.getFilesByName(fileName).next();
  const dataBlob = file.getBlob();
  const csvString = dataBlob.getDataAsString();
  const csvData = Utilities.parseCsv(csvString);
  const sheet = SpreadsheetApp.getActiveSheet();
  const allData = sheet.getDataRange().getValues();

  // I modified below script.
  const obj = allData.reduce((o, r) => (o[r.join("")] = true, o), {});
  const values = csvData.filter(r => !obj[r.join("")]);
  if (values.length > 0) {
    sheet.getRange(sheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
  }
}

• When this script is run, the duplicated values are checked between allData and csvData. And, the rows without the duplication are appended to the active sheet.

• This modified script checks the duplicated rows by all columns. When you want to check the specific columns, please tell me.

References:

• reduce()