insert formulas in the last row of a sheet-CodePudding

I have a script that fetches data (formulas) from a worktab and copies it to the second row of the calling worktab. This row two is inserted before, this works. Maybe a bit slow but that is a different topic. But now I want to insert a row as the last row and copy the formulas there. I have managed to insert the last row. I have no idea how to do the rest. Attached below the script So I would like to insert instead of in cell L2, in cell L Lastrow the formula from the sheet Formulas A2.

function NewT() {
  // activate the T sheet
  var spreadsheet = SpreadsheetApp.getActive();
  var TSheet = spreadsheet.getSheetByName('T');
  TSheet.activate();  
    
// Insert last row
   var numberrows = TSheet.getMaxRows()
   TSheet.insertRowsAfter(numberrows,1)

  // Now fill in the formulas for the Trade
  var formulas = spreadsheet.getSheetByName('Formulas');
  
  // declare a two-dimensional area of (TSheet, formulas) cell references
  // then just iterate through the array copying from formulas to Tsheet
  var myMap = [ ['L2', 'A2'],
               ['M2', 'A3'], 
               ['N2', 'A4'], 
               ['O2', 'A5'], 
               ['P2', 'A6'], 
               ['Q2', 'A7'], 
               ['R2', 'A8'], 
               ['S2', 'A9'], 
               ['T2', 'A10'], 
               ['U2', 'A11'], 
               ['V2', 'A12'], 
               ['W2', 'A13'], 
               ['X2', 'A14'], 
               ['Y2', 'A15']];
              // ['AE2', 'A21']];
               
    var i;
    var l = myMap.length;
  for (i = 0; i < l; i   ) {
    TSheet.getRange(myMap[i][0]).setValue(formulas.getRange(myMap[i][1]).getValue());
    
  }

CodePudding user response：

function NewT() {
  // activate the T sheet
  var spreadsheet = SpreadsheetApp.getActive();
  var TSheet = spreadsheet.getSheetByName('T');
  TSheet.activate();  
    
// Insert last row
   var numberrows = TSheet.getMaxRows()
   TSheet.insertRowsAfter(numberrows,1)

  // Now fill in the formulas for the Trade
  var formulas = spreadsheet.getSheetByName('Formulas');
  
  // declare a two-dimensional area of (TSheet, formulas) cell references
  // then just iterate through the array copying from formulas to Tsheet
  var myMap = [ ['L2', 'A2'],
               ['M2', 'A3'], 
               ['N2', 'A4'], 
               ['O2', 'A5'], 
               ['P2', 'A6'], 
               ['Q2', 'A7'], 
               ['R2', 'A8'], 
               ['S2', 'A9'], 
               ['T2', 'A10'], 
               ['U2', 'A11'], 
               ['V2', 'A12'], 
               ['W2', 'A13'], 
               ['X2', 'A14'], 
               ['Y2', 'A15']];
              // ['AE2', 'A21']];
               
    var i;
    var l = myMap.length;
  for (i = 0; i < l; i   ) {
    TSheet.getRange(myMap[i][0]).setFormula(formulas.getRange(myMap[i][1]).getFormula());
    
  }
}

CodePudding user response：

Description

I have included only the portion to copy formulas (as text) to the last row. It is how I would do it for any row. Rather then do each row in a loop I would get the values/formulas I want with one instruction in this case var formulas = TSheet.getRange(2,1,14,1).getFormulas(); // Get A2:A15. This gives a 2D array of [[A2],[A3],[A4],...]. i Then I convert to a 1D array using Array.forEach(), [A2,A3,A4,...]. Now I can put the formulas any where I want with setFormulas(). I put the transpose array within array brackets [transpose] to create a 2D array for setFormulas().

This assumes that there are no relative formula references that do not copy properly.

Script

function NewT() {
  // activate the T sheet
  var spreadsheet = SpreadsheetApp.getActive();
  var TSheet = spreadsheet.getSheetByName('T');
    
  // Now fill in the formulas for the Trade
  var formulas = spreadsheet.getSheetByName('Formulas');

  formulas = formulas.getRange(2,1,14,1).getFormulas(); // Get A2:A15
  var transpose = [];
  formulas.forEach( row => transpose.push(row[0]) ); // transpose [[1],[2],[3]] to [1,2,3]
  // L is column 12
  Tsheet.getRange(Tsheet.getLastRow() 1,12,1,transpose.lenth).setFormulas([transpose]);  // L(last row  1):Y(last row  1)
}

Reference