Building Google Sheets macros for auto update

Question

I want to make a self-updating Google sheet to use in my home project. here's a basic breakdown: I have a Google sheet file with multiple worksheets (tabs). I Want to add macros that will do the following:

If I add value to a sheet which is called 'incoming' I want the value (row/column) to be added automatically to all the other sheets: 1..n
Once a new item is added to a sheet (the base assumption is that all data in the sheet is the same format) I want the sheets, to sort themselves with the new data (based on a rule I will give them)

currently, I have the Google sheet set up but I wasn't able to find a way to auto update Below is my code:


function updateOnNewRow() {
  //#TODO: Add a trigger gor a specific sheet in the spreadsheet 
  var updatesSheet ="";
  var notificationEmail ="";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var updatedSheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  var lastRow = sheet.getLastRow();
  var numRows = 1;   // Number of rows to process
  var cols = sheet.getLastColumn();
  // Fetch the range of cells
  var dataRange = sheet.getRange(lastRow, 1, numRows, cols)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();

  // Fetch your row as an array
  var lastRowData = data[0];
  var newEntry = lastRowData.join(",");

  // get all sheets
  var sheetNameArray = [];
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheetNameArray = sheets.map(function(sheet){
    return [sheet.getName()];
  });  
  // cycle through sheets :   
  for (var priceSheet in sheets)
  {
    //ignore 'updates' sheet
    var currentSheet = sheets[priceSheet].getSheetName();
    if (currentSheet==null)
      {
        // Handle sheet name failure
        var errorMessage = "getSheetByName returend null";
        Logger.log(errorMessage);        
      }
    else if (currentSheet != updatedSheetName) 
    {
      //get Sheet
      var formattedData = []
      var workingSheet = SpreadsheetApp.getActive().getSheetByName(currentSheet);
      // Get all populated rows in sheet:
      var workDataRange = workingSheet.getDataRange().getValues();      
      // created formatted data array
      for (var i = 1; i < workDataRange.length; i++) {
        var row = workDataRange[i];
        var dataRow = row.join(",");
        formattedData.push(dataRow)
        Logger.log(row + " : Added to the array");
      } 
      if (formattedData.indexOf(newEntry)<0)
      {
        workingSheet.appendRow(lastRowData);
        Logger.log("Appended to sheet: "+currentSheet+"; Row:"+newEntry);
      }
    }  
  }
  // Format data by separating values into comma separated list for email
  //var emailContent = lastRowData.join(",");
  // Send an email (change this to your email)
  //MailApp.sendEmail(notificationEmail, 'Email subject', emailContent);
  // Log contents for debugging      
 // Logger.log(emailContent);
}

Data example in the doc

Main Sheet: Distillery | Type | Volume | Comment | Cheapest Distributor | price | Distributor 1|...| Distributor N

Distirbutor Sheet: Distillery | Type | Volume | Comment | Lowest Price | Update_Date 1 | ... | Update_Date N

Updates Sheet/New Data Sheet : Distillery | Type | Volume | Comment | Price | Distributor| Date| Updated (Y/N)

Added my code , but i think a better approach would using a object oriented approach to creat an object of the new entry i want to circulate through various sheets in the doc: — Bored002
@ziganotschka beat me to suggesting onEdit(event). So far as sorting is concerned, it would be helpful if you would edit your question to describe the type(s) of sort that you might propose, and how/when you would expect to express these. — Tedinoz

ziganotschka ziganotschka · Accepted Answer · 2019-07-11T15:00:58

Apps Script provides you automatic trigger functions, so onEdit(e) allows you to perform an action (e.g. append values) when an editing event takes place. Thereby, (e) is the event object, with arguments like e.range (the range that was edited).

You can access the values, the row and the column of e.range. In other words - you can build a function that triggers automatically every time a range is modified and appends the values of the range to the corresponding range in your workingSheet.

Building Google Sheets macros for auto update

1 Answers