Copy row to another tab, getFormulas, clearContent, setFormulas

Question

I'm trying to make google sheets automatically copy a row and paste it to the bottom of another tab when Completed is selected from a dropdown in the 8th column. Then I want it to grab the formulas from that row, clear the row, and then replace the formulas. The code works as far as copying and pasting to the other tab but I can't get it to grab the formulas, clear the row and then replace the formulas. I can't even get it to clear the row. The code is correctly executing everything but the last four lines.

This isn't my code. I got it from a website and modified it to fit my sheet. I have a very basic understanding of javascript. I need the original row to remain with just the formulas populated so that it can be reused. I'm using copyTo instead of moveTo because the row is being referenced upstream by another sheet and moveTo causes the formulas on the upstream spreadsheet to auto update to the new location of the data instead of continuing to reference the original row. Thank you for any help.

function myStorage() {

 // moves a row from a sheet to another when a value is entered in a column

 var sheetNameToWatch = "In Progress";

 var columnNumberToWatch = 8;

 var valueToWatch = "Completed";

 var sheetNameToMoveTheRowTo = "Storage";



 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var sheet = SpreadsheetApp.getActiveSheet();

 var range = sheet.getActiveCell();



 if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

   var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);

   var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

   sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange);

// this is where it stops working.  I'm not sure how to grab just the row of the active cell and do the following

   var clearRange = sheet.getRange(range.getRow());

   clearRange.getFormulas();

   clearRange.clearContent();

   clearRange.setFormulas();
 }

}

Cooper Cooper · Accepted Answer · 2019-11-26T21:45:16

Try this:

function CallItSomethingElse(e) {
  var sh=e.range.getSheet();
  if (sh.getName()=="In Progress" && e.range.columnStart==8 && e.value=="Completed") {
    var targetSheet = e.source.getSheetByName("Storage");
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).copyTo(targetRange);
    var clearRange=sh.getRange(e.range.rowStart,1,1,sh.getLastColumn());
    var fA=clearRange.getFormulas();//returns a 2d array of formulas
    clearRange.clearContent();
    clearRange.setFormulas(fA);//you have to add that array back into this line
  }
}

Because many people will take this code and try to run it from the editor just to return and tell me that they get the error of cannot find method getSheet of undefined. Let me first suggest that you can't run this code without an event object. The single e is the parameter that the event object will be place into. Without the e the program cannot run. Also unless there is a trigger to create the e the script cannot run. So if you wish to run this from the script editor then you will have to provide the event object. I don't choose to do that so when I debug these things I do it will a live trigger.

Copy row to another tab, getFormulas, clearContent, setFormulas

1 Answers