CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-formula
Awais Anwar
Awais Anwar

Reputation: 31

Trying to create a custom fuction using app script that can automatically copy formula to respective row or column

I am trying to build a custom function using an app script that can automatically copy formula to respective rows or columns.

Closest I could find is "Onedit" but it is not provided with a function like an array formula which does the same job for arrays. On edit copies any formula I put in the sheet to all rows... What if I want to copy that formula in specific ranges only? Cant it be done through a custom function?

A sample table is provided below. I need a formula that can copy formula in all rows below automatically using a custom function.

enter image description here

formula i use in cell above that gives 366,335 as as below. It super complex so do not support array formula. i expect formula to change cell references as it is copied to different cells.

=if(sumifs(indirect(Address(match($P$55,$P$1:$P$55,0)+1,match($B$6,$A$55:$AB$55,0))&":"&LEFT(Address(match($P$55,$P$1:$P$55,0)+1,match($B$6,$A$55:$AB$55,0)),2)),indirect(Address(match($P$55,$P$1:$P$55,0)+1,match($B$8,$A$55:$H$55,0),4)&":"&LEFT(Address(match($P$55,$P$1:$P$55,0)+1,match($B$8,$A$55:$H$55,0),4),1)),$B14,indirect(Address(match($P$55,$P$1:$P$55,0)+1,match($B$9,$A$55:$H$55,0),4)&":"&LEFT(Address(match($P$55,$P$1:$P$55,0)+1,match($B$9,$A$55:$H$55,0),4),1)),C$13,$B$56:$B,">"&$B$7,$B$56:$B,"="&$C$7)=0,"",sumifs(indirect(Address(match($P$55,$P$1:$P$55,0)+1,match($B$6,$A$55:$AB$55,0))&":"&LEFT(Address(match($P$55,$P$1:$P$55,0)+1,match($B$6,$A$55:$AB$55,0)),2)),indirect(Address(match($P$55,$P$1:$P$55,0)+1,match($B$8,$A$55:$H$55,0),4)&":"&LEFT(Address(match($P$55,$P$1:$P$55,0)+1,match($B$8,$A$55:$H$55,0),4),1)),$B14,indirect(Address(match($P$55,$P$1:$P$55,0)+1,match($B$9,$A$55:$H$55,0),4)&":"&LEFT(Address(match($P$55,$P$1:$P$55,0)+1,match($B$9,$A$55:$H$55,0),4),1)),C$13,$B$56:$B,">"&$B$7,$B$56:$B,"<="&$C$7))

Thanks

Upvotes: 0

Views: 98

Answers (2)

Kristkun
Kristkun

Reputation: 5953

It is not possible to set cell value using custom formula. You might want to consider using custom menu with a custom dialog

Sample Code:

CopyFormulaPage.html

<!DOCTYPE html>
<html>
  <script>
    function Submit(){
      var src = document.getElementById("source").value;
      var dst = document.getElementById("destination").value;
      google.script.run.withSuccessHandler(onSuccess).CopyFormula(src,dst);
    }

    function onSuccess(){
      var inDiv = document.getElementById('input');
      var outDiv = document.getElementById('output');
      outDiv.innerHTML = 'Formula copied successfully';
      inDiv.innerHTML = '';
    }

  </script>
  <body>
    <div id="output"></div><br>
    <div id="input">
      <label for="source">Source Range:</label><br>
      <input type="text" id="source" name="source"><br><br>
      <label for="destination">Destination Range:</label><br>
      <input type="text" id="destination" name="destination"><br><br>
      <button onclick='Submit()'>Submit</button>
    </div>
  </body>
</html>

Code.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Copy Formula', 'ShowDialog')
      .addToUi();
}

function ShowDialog(){
  var html = HtmlService.createHtmlOutputFromFile('CopyFormulaPage')
      .setWidth(300)
      .setHeight(180);
  SpreadsheetApp.getUi().showModalDialog(html, 'Copy Formula Dialog');
}

function CopyFormula(src,dst){
  Logger.log(src+" "+dst);
  var sheet = SpreadsheetApp.getActiveSheet();
  var dstRange = sheet.getRange(dst);
  sheet.getRange(src).copyTo(dstRange);
}

What it does?

  1. Get source range and destination range from the custom dialog. Then run CopyFormula() in the server with success handlers using google.script.run.withSuccessHandler()
  2. Get the destination range.
  3. Get the source range then use copyTo(destination) to copy the formula to your destination range

Output

enter image description here

  • I noticed in your based formula that most of the cell references were locked besides $B14. As shown in the output, the copied cell adjusts the unlocked reference to $B15, $B16, and so on..

Upvotes: 1

idfurw
idfurw

Reputation: 5852

I think you should debug it yourself step by step.

Remove all $ before each row number except those act as header row or a constant cell.

To apply with Google Apps Script.

The simplest way is to create with setFormulas(formulas).

The second way would the using replace to replace the row number of existing formula but the conditional is that you only added $ to those rows do not change.

You should be looking for ARRAYFORMULA.

It works by putting a single formula like this in B2.

onEdit is not needed in this case.

=ARRAYFORMULA(IF(A2:A="",,VLOOKUP(A2:A,Data!A:B,2,FALSE)))

Upvotes: 1

Related Questions