0

I need a function that will allow to recopy what is already written in a cell. I'd call it NothingHasChanged() and I'd use this way:

In a cell chosen randomly I'd type:
if(A1="Yes"; "The cell A1 contained Yes at least once"; NothingHasChanged() )

This way i'd keep history of the cell value overtime.

I tried to do a script :

function FormulaToValueV2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(599, 0).activate();
  spreadsheet.getCurrentCell().offset(-599, 0).copyTo(spreadsheet.getActiveRange(), 
SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getCurrentCell().offset(-599, 0).activate();
  spreadsheet.getCurrentCell().offset(599, 0).copyTo(spreadsheet.getActiveRange(), 
SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

that I generated from a macro I did by copying as a value to override the formula but it throws me an error saying that I don't have the permission to "copyto".

I'm desperate actually, I'm not a programmer

Thanks for the help and stay safe

EDIT : Here is a link with no personal datas of what i'm trying to do.

https://docs.google.com/spreadsheets/d/19-Jv2kDUXxMXUid1EqDmwceZXU3w6U2Nd5uqwr3Swes/edit#gid=0

As you can see, i'd like to overcome the circular reference problem : if the condition is not met, you don't change the value that was in the cell. This way, i can keep track of the datas contained in "actual Day" into "saved Days".

monax
  • 1
  • 1
  • did you see https://stackoverflow.com/questions/36837005/you-do-not-have-permission-to-use-copyto ? – bruno May 03 '20 at 19:47
  • Hello Bruno, thanks for the reposnse. Yes i did! but i didn't understand, i don't wish something as complicated as this request. I actually searched for a whole day on the internet, before asking but either it was not what i was looking for or just too complicated and i didn't understand. – monax May 03 '20 at 19:50
  • Where do you plan to keep your history? – Cooper May 03 '20 at 20:00
  • This is the real function : =if(AND($R1=TODAY();$R1='Tampon 1'!$D$4);'Q123 - Marcinelle'!R11;R11) What i'm trying to do, is to, is to recopy the content from the table in the tab "Q123 Marcinelle" into the exact same tab but with no content at first. But i'd like to have this new table to get filled day by day. Also, thanks a lot for the help – monax May 03 '20 at 20:06
  • I'd like to find something that would replace the last argument where in the cell R11, i just write R11.... since it is recognized as a circular reference. – monax May 03 '20 at 20:16
  • Hi i just made an edit with a link to a sheet with my problem so that you can understand better – monax May 04 '20 at 08:15
  • Hello! If I understood correctly, you want to run a script once a day, that will check if there is a new value ("Yes" in A1?) in Actual Days, and if so, copy the D column to Saved Days. Is it correct? – Jescanellas May 04 '20 at 11:04
  • Hello! I want a function or any other way to leave a cell unchanged (ie: keep the value the formula had once calculated) if the condition to calculate doesn't work anymore. This way the value would stay forever – monax May 04 '20 at 11:30
  • But would the value be in the same Cell so the formula there wouldn't work anymore? Or would it save it in another Sheet? It's a bit hard to understand without a clear example of the final result. – Jescanellas May 06 '20 at 09:27

0 Answers0