How to append new values from master Google sheet onto multiple sheet with conditions

Question

I have a Master Google Sheet with five columns that automatically gets updated daily.

Date    Event   Value1  Value2  Value3
May 1   ABC     A       Yes     Yes
May 1   ABC     B       Yes     Yes
May 1   ABC     C       Yes     Yes
May 1   DEF     A       Yes     Yes
May 1   DEF     B       Yes     Yes
May 1   GHI     A       Yes     Yes
May 1   GHI     C       Yes     Yes
May 2   ABC     A       Yes     Yes
May 2   DEF     D       Yes     Yes
May 2   GHI     C       Yes     Yes

The same document has a corresponding sheet for the three types of events in the Master Sheet: ABC, DEF and GHI. These sheets have existing values in it.

I would like a Google Apps Script to only append unique and new values from Value1 to corresponding Event sheets on edit of the Master sheet. Example:

Sheet ABC

Value
A
B
C

Sheet DEF

Value
A
B
D

Sheet GHI

Value
A
C

It's not really dependent on the date, but rather looking through what already exists in the corresponding sheets.

I'm a complete beginner in Google Apps Scripts. I've looked into other solutions and the furthest I've gone is to find how to dynamically create the different sheets based off of the Event value from here.

The only issue is that this copies over all rows which means I get duplicates in my other sheets.

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName('Raw');
  var data_range = masterSheet.getRange('B:C').getValues();
  // Get headers row (to be appended on top when new sheet is created):
  var headers = data_range[0];
  // Iterate through all rows of data from Raw:
  for (var i = 1; i < data_range.length; i++) {
    var row = data_range[i]; // Current row
    var eventName = row[0]; // Event name
    // Get sheet with current event name:
    var sheet = spreadsheet.getSheetByName(eventName);
    // Check if sheet with current event name exists. If it doesn't it creates it:
    if (!sheet) {
      sheet = spreadsheet.insertSheet(eventName);
      sheet.appendRow(headers);
    }
    // Appends current row of data to the new sheet:
    sheet.appendRow(row);

  }
}

I just need help changing the sheet.appendRow(row); line to only pull unique values.

I am not sure, but this addon could help gsuite.google.com/u/0/marketplace/app/flow/909859924833 — Anees Hameed

ziganotschka ziganotschka · Accepted Answer · 2020-06-01T13:53:44

You need to implement a conditional statement verifying either an entry with the same value in row[1] exists already
For this you have to loop through the rows of the destination sheet in the same way already do for the master sheet
Transform the value range to a simple array with flat()
Compare the entries in column B (?) against row[1] with indexOf()

Sample:

function createNewSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName('Raw');
  var data_range = masterSheet.getRange(1,2,masterSheet.getLastRow(),2).getValues();
  // Get headers row (to be appended on top when new sheet is created):
  var headers = data_range[0];
  // Iterate through all rows of data from Raw:
  for (var i = 1; i < data_range.length; i++) {
    var row = data_range[i]; // Current row
    var eventName = row[0]; // Event name
    // Get sheet with current event name:
    var sheet = spreadsheet.getSheetByName(eventName);
    // Check if sheet with current event name exists. If it doesn't it creates it:
    if (!sheet) {
      sheet = spreadsheet.insertSheet(eventName);
      sheet.appendRow(headers);
    }
    //retrieves values in column A of the destination sheet
    var sheetValuesB = sheet.getRange(1,2,masterSheet.getLastRow(),1).getValues();
    //transforms the values into a 1-D array
    sheetValuesB = sheetValuesB.flat();
    //check if row[1] already exists in sheet
    if(sheetValuesB.indexOf(row[1]) == -1){
      // Appends current row of data to the new sheet:
      sheet.appendRow(row);
    }        
  }
}

Annotation:

I encourage you define the range setting as the limit getLastRow() which retrieves the last row containing data, whilst the notation B:C would also iterate through all the empty rows and make your script unnecessary slow.

How to append new values from master Google sheet onto multiple sheet with conditions

1 Answers