-1

I have a sheet titled 'Raw_Data' that I insert data into every day by inserting new rows above the data from the last day. I have another sheet titled 'Overall_Formulas' that populates formulas based on the data that is in the 'Raw Data' sheet. Right now there are arrayformulas that populate all the cells in "Overall_Formulas" when "Raw_Data" is updated. However, I am trying to get away from that so that I can go back and edit specific cells in "Overall_Formulas" individually. -- You can't do that with arrayformulas.

What I'm trying to accomplish is when I add new rows of data to 'Raw_Data', a custom script runs and inserts the same amount of new rows into the 'Overall_Formulas' sheet with the specific formulas I have for each cell.

For example: Let's say I inserted 2 new rows (starting on the 2nd row) into "Raw_Data" and then filled those 2 new rows with the data I have. From there, 2 new rows would also be inserted (via onEdit script) into the top of the sheet "Overall_Formulas" with columns A2:S3 filled with specific formulas for each cell. Each cell in "Overall_Formulas" would then be the following :

("A2").setFormula('=timevalue(text(F2, "hh:mm:ss")');
("A3").setFormula('=timevalue(text(F3, "hh:mm:ss")');
("B2").setFormula('=timevalue(text(F2, "hh:mm:ss")');
("B3").setFormula('=timevalue(text(F3, "hh:mm:ss")');
("C2").setFormula('=(text(D2, "dddd")');
("C3").setFormula('=(text(D3, "dddd")');
("D2").setFormula('= text(F2, "mm/dd/yyyy")');
("D3").setFormula('= text(F3, "mm/dd/yyyy")');
("E2").setFormula('=text(F2, "MMMM")');
("E3").setFormula('=text(F3, "MMMM")');
("F2").setFormula('=Raw_Data!A2');
("F3").setFormula('=Raw_Data!A3');
("G2").setFormula('=Raw_Data!C2');
("G3").setFormula('=Raw_Data!C3');
("H2").setFormula('=Raw_Data!L2');
("H3").setFormula('=Raw_Data!L3');
("I2").setFormula('=Raw_Data!W2');
("I3").setFormula('=Raw_Data!W3');
("J2").setFormula('=K2/H2');
("J3").setFormula('=K3/H3');
("K2").setFormula('=IF($G2="withdrawal", ($M2-$P2)-$H2, ($H2-($L2+$P2)))');
("K3").setFormula('=IF($G3="withdrawal", ($M3-$P3)-$H3, ($H3-($L3+$P3)))');
("L2").setFormula('=if(Raw_Data!$AC2=0, "UNKNOWN", Raw_Data!AB2-Raw_Data!AC2)');
("L3").setFormula('=if(Raw_Data!$AC3=0, "UNKNOWN", Raw_Data!AB3-Raw_Data!AC3)');
("M2").setFormula('=Raw_Data!M2*Raw_Data!O2');
("M3").setFormula('=Raw_Data!M3*Raw_Data!O3');
("N2").setFormula('=(If($G2="withdrawal", 0, $L2-$M2)');
("N3").setFormula('=(If($G3="withdrawal", 0, $L3-$M3)');
("O2").setFormula('=$N2/$M2');
("O3").setFormula('=$N3/$M3');
("P2").setFormula('=Raw_Data!Q2*Raw_Data!AE2');
("P3").setFormula('=Raw_Data!Q3*Raw_Data!AE3');
("Q2").setFormula('=$P2/$H2');
("Q3").setFormula('=$P3/$H3');
("R2").setFormula('=Raw_Data!E2');
("R3").setFormula('=Raw_Data!E3');
("S2").setFormula('=Raw_Data!F2');
("S3").setFormula('=Raw_Data!F3');
  • See stackoverflow.com/questions/12036726/… – Rubén Aug 18 '15 at 13:53
  • AFAIK the standard procedure for adding new raw data is to append it, in other words add below, instead of above of existing rows. In an comment to my answer few hours ago you mentioned that the new data could be added to the end. Please update your question accordingly. Also could be useful that you add a demo spreadsheet including some demo data and broad details about your coding knowledge like the use of variables, cell references, loops handling, etc., or if you already found a similar Q&A but needs help to adapt it to your needs. – Rubén Aug 18 '15 at 19:46
  • Please, before updating your question, read webapps.stackexchange.com/help/how-to-ask – Rubén Aug 18 '15 at 19:47
0

Overview

There are two main situations:

  1. How to replicate the number of rows inserted
  2. How to set the corresponding formula to each cell

1. How to replicate the number of rows inserted

One alternative among others is to use the Change Google Sheets installable event instead of OnEdit. The Change event has the property changeType. Your project could use it to know if rows were inserted to the bounded spreadsheet. To get this event you should create a function and add it as an installable trigger.

Below there is a sample code and the instructions to manually add an installable trigger to a Google Apps Script bound to an spreadsheet project.

You could add the code to add the formulas to each corresponding cell after the rows were inserted in the 'Overall_Formulas' sheet.

Code

// Global variables
var LEADER_SHEET = 'Raw_Data';
var FOLLOWER_SHEET = 'Overall_Formulas';

function replicateRowInsertion(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  if(e.changeType == 'INSERT_ROW'){
    var leaderRange = ss.getActiveRange();
    var rows = leaderRange.getNumRows();
    if(leaderRange.getSheet().getName() == LEADER_SHEET){
      var sheet = ss.getSheetByName(FOLLOWER_SHEET);
      sheet.insertRows(leaderRange.getRow(),rows);
    }
  }
}

Instructions

Installable triggers could be added, manually or automatically. In order to keep this answer short, only the manually instructions are included. Take a look to the source to learn the automatically instructions and other important details like restrictions, kind of installable triggers, limitations, etc.

From Installable Triggers - Apps Script

Managing triggers manually

To manually create an installable trigger through a dialog in the script editor, follow these steps:

  1. From the script editor, choose Resources > Current project's triggers.
  2. Click the link that says: No triggers set up. Click here to add one now.
  3. Under Run, select the name of function you want to trigger.
  4. Under Events, select either Time-driven or the Google App that the script bound to (for example, From spreadsheet).
  5. Select and configure the type of trigger you want to create (for example, an Hour timer that runs Every hour or an On open trigger).
  6. Optionally, click Notifications to configure how and when you will be contacted by email if your triggered function fails.
  7. Click Save.

2. How to set the corresponding formulas to each cell

One alternative is to add a third sheet to hold the formulas to be copied and pasted to the Overall_Formulas sheet. Doing this, maintaining the formulas to be added to new rows adding will be more easy than updating formulas included in the code and just require few additional lines to the previous code.

Code

The following code assumes that the sheet Template holds the formulas in the second row.

// Global variables
var LEADER_SHEET = 'Raw_Data';
var FOLLOWER_SHEET = 'Overall_Formulas';

/* Global variables to idenfify the location of the formulas to be copied
   and pasted */
var SOURCE_SHEET = 'Template';
var SOURCE_RANGE = '2:2';

function replicateRowInsertion(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  if(e.changeType == 'INSERT_ROW'){
    var leaderRange = ss.getActiveRange();
    var rows = leaderRange.getNumRows();
    if(leaderRange.getSheet().getName() == LEADER_SHEET){
      var sheet = ss.getSheetByName(FOLLOWER_SHEET);
      sheet.insertRows(leaderRange.getRow(),rows);
      // Copy and paste the formulas
      var sourceRange = ss.getSheetByName(SOURCE_SHEET).getRange(SOURCE_RANGE);
      var i = leaderRange.getRow();
      for(i; i < leaderRange.getRow() + rows; i++ ){
        sourceRange.copyTo(sheet.getRange(i + ':' + i));
      }
    }
  }
}
  • Thanks @Rubén for the answer! There are a couple of issues that I am running into. One, is that there is an undefined variable 'e' on line 12. Not sure what that is supposed to be? Also, the formulas I have should really never change. Is there a way to have the formulas needed to be added in, in the code itself? And will they apply themselves to the all of the rows added by the script in "Overall_Formulas" that were added in "Raw_Data"? – Cailen Sullivan Aug 17 '15 at 22:51
  • The undefined variable 'e' on line 12 occurs because the input parameter 'e' it's not being passed. If you added specified onChange trigger, it will pass the required parameter every time that a change is made to the bounded spreadsheet. To run this function from the Script editor, you should create a function that populates the required properties. Regarding the second question, yes there is a way, actually there are several ways to have the formulas in the code itself. I will add more comments about this later. – Rubén Aug 17 '15 at 23:25
  • Thanks Rubén. I'll wait to see your next comment on the formulas in the code itself. – Cailen Sullivan Aug 18 '15 at 0:52
  • Ruében I just realized something! And hopefully this makes my question a bit easier...... The reason I needed my data to be inserted as rows above is because I was using array formulas that I could not "filter". However, now that we will be pasting formulas (via script) into every cell and every row, the data will be able to be filtered. This means that I can add the new data I have to the bottom of the "Raw_Data" sheet. So really at this point I just need "Overall_Formulas" to insert formulas with the same amount of rows of data that were inserted at the bottom of "Raw_Data" – Cailen Sullivan Aug 18 '15 at 4:14
  • @CailenSullivan. Thanks for sharing what you just realized. As your comment include details regarding your question, please update it. – Rubén Aug 18 '15 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.