How to only copy array elements to spreadsheet if conditions met Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of How to only copy array elements to spreadsheet if conditions met without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I am building a script to copy CSV data to a Google Sheet. The CSV is converted to a 2D Array trades, and a forEach loops through each element/row of trades, named trade. I’m trying to make it check if the data is in the spreadsheet, and if it is, skip copying that entry. The way I’m identifying each trade is by the date (column A [or 0]) and trade index (column W [or 22]). If the trade has the same date and trade index, it should not be copied. As I have it now, it runs correctly against test data in the sheet (e.g. if I change the first entry, ticker UUU, to have the same date & index as one of the entries in the array), but when run a second time, it does not recognize the previously added entries (with identical dates & indexes) and it continues to copy duplicates.

How can I improve this code to work as intended and not copy duplicates, contrary to what is intended? How can I test rows in sheet and only copy entries from the array that are not already present in the sheet?

Below is the relevant code I’m working with.

This is a copy of the google sheet I’m playing with. The first entry is the test data mentioned above. The three subsequent entries are the result of the test array being copied using the below code (and other less relevant code).

const ss = SpreadsheetApp.openById('###');
let log = ss.getSheetByName("Log");

trades.forEach(function(trade) {

      //Housekeeping code deleted

      //Relevant code:

      let data = log.getDataRange().getValues();
      for (let i = 1; i < data.length; i++){

        let dateRange = log.getRange("A:A");
        let dateValues = dateRange.getValues();
        let numberRange = log.getRange("W:W");
        let numberValues = numberRange.getValues();
        let date = Utilities.formatDate(new Date(dateValues[i]), "PST", "yyyy-MM-dd");
        let tradeNum = numberValues[i];

        if(date == trade[0] && tradeNum == trade[22]) {
          break;
        };

        let insertRow = log.getDataRange().getValues().length + 1; //Non-index (saved already above)
        let importedData = log.getRange(insertRow,1,1,trade.length).setValues(new Array(trade));
        return insertRow, importedData;
      };

      return insertRow;

    });

UPDATE

Thanks to Abdellah’s well written answer and Neven’s helpful advice, the following solution is working perfectly:

    //Get existing trades
    let loggedTrades = log.getDataRange().getValues().slice(1); // Removed header

    //Method to join date & trade index to create unique trade ID
    const createId = (trade) =>
      Utilities.formatDate(new Date(trade[0]), "GMT", "yyyy-MM-dd") + trade[22];

    //Map new array of existing trades' unique trade IDs
    let loggedTradesId = loggedTrades.map(trade => 
      createId(trade));
    
    //Filter to create new array of elements in trades array that are not already in sheet (in loggedTrades)
    let newTrades = trades.filter(
      (trade) => !loggedTradesId.includes(createId(trade))
    );

    //Import newTrades to sheet 
    if(newTrades.length > 0) {
      
      let insertRow = log.getLastRow() + 1; //Non-index (saved already above)
      let importedData = 
      log.getRange(
        insertRow,
        1,
        newTrades.length,
        newTrades[0].length
      ).setValues(newTrades);
    };

Answer

Here’s a solution that:

  1. gathers the existing rows focusing on the relevant fields in a Set for faster lookup.
  2. filter the csv rows based on the same logic.
  3. appends everything to the log sheet in one go.
const ss = SpreadsheetApp.openById("###");
let log = ss.getSheetByName("Log");

const stringyfyTrade = (trade) =>
  trade[22] + Utilities.formatDate(trade[0], "PST", "yyyy-MM-dd");

// create a set of existing trades (from the trades date+index) for faster lookup
let alreadyLoggedTrades = new Set(
  log
    .getDataRange()
    .getValues()
    // get rid of header row
    .slice(1)
    .map(stringyfyTrade)
);

// gather non logged trades in one go
let nonLoggedTrades = trades.filter(
  (trade) => !alreadyLoggedTrades.has(stringyfyTrade(trade))
);

// append them if there are any
if (nonLoggedTrades.length > 0) {
  s.getRange(
    s.getLastRow() + 1,
    1,
    nonLoggedTrades.length,
    nonLoggedTrades[0].length
  ).setValues(nonLoggedTrades);
}
We are here to answer your question about How to only copy array elements to spreadsheet if conditions met - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji