0

I might not be able to explain better, but I will try my best. I have two columns say A and B, in column A there are formulas so values in them get changed depending on some other conditions, Now what I want in column B is to paste/setvalue the value for the first time whenever a value appears in column A, so that when there are any further changes in column A, it wouldn't affect the value that is pasted in column B. Although I have tried my best to write the script, it does paste the value but it pastes in all the column and does not care if any cell in column A is empty.

I have gone through a lot of research but could not find an answer. Although OnEdit can work but as the column from which the value is to be got has formulas in it and OnEdit doesn't work on formulas. So once the script is corrected, we can trigger it to time driven.

I just need the help to make this function work correctly, I will be highly thankful for any help. thank you.

function pastevalue(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
  var lastrow = ss.getRange("A2:B").getValues().filter(String).length;
  var range = ss.getRange(2,1,lastrow);
  var pasterange = ss.getRange(2,2,lastrow);
  var values = range.getValues().filter(String);
  var pastevalues = pasterange.getValues();

  for (i=0; i<values.length; i++){
    if(pastevalues[i] == ""){
        var value = pasterange.setValue(values[i])
    }
  }
}
Mubashir Rehman
  • 61
  • 2
  • 8
  • 1
    Does this help answer your question? [Trigger a script when a formula changes a cell value](https://stackoverflow.com/questions/42815059/trigger-a-script-when-a-formula-changes-a-cell-value) – Thum Choon Tat Jan 07 '22 at 10:58
  • Thank you so much for the response. I had seen it before raising the question. To implement it I will have to add a new sheet may be. I want to try a script that runs without adding a sheet. – Mubashir Rehman Jan 08 '22 at 14:45

1 Answers1

0

I presume that your goal is to grab the numerical value of a formula immediately after you enter it, so that if the displayed value in the cell of the formula changes in the future, you still possess the original value that the formula yielded. The following script accomplishes that:

function onEvent(e){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  if (e.range.getColumn() == 1 && typeof e.oldValue == 'undefined'){
    sheet.getRange(e.range.getRow(), 2).setValue(e.range.getValue());
  }
}

Note that it is an event trigger, meaning it runs every time the spreadsheet is, well, edited. As the parameter of the function we grab the event that triggered the script (you can read how they are structured here), and then we check for two conditions that both have to be true to copy the value into the B column:

  • First we check that the column of the cell modified is the A (1st) column.
  • Then we check that the cell was blank before modification. This will mean that the value will only be copied when a formula is written in a blank cell.
Oriol Castander
  • 640
  • 1
  • 5
  • Thank you so much for the response. I have tried it, but the event is not firing without any trigger. If I trigger it on change then it runs but it throws this ```TypeError: Cannot read property 'getColumn' of undefined``` – Mubashir Rehman Jan 08 '22 at 14:43
  • You have to set up a trigger, the script will not work if you simply try to run it. You can read more about triggers [here](https://developers.google.com/apps-script/guides/triggers). How to set up a trigger is explained [here](https://support.google.com/tagmanager/answer/7679316) – Oriol Castander Jan 10 '22 at 16:09