Google Sheet script/Macro to return a formula if cell content is deleted

[jimbogarner]

Hi,

I have got an index match formula that returns a name depending on various criteria, however, some of these may need to be manually overwritten - the tab is generally duplicated for each new week, therefore there is a risk someone forgets that there is a hardcoded name in the file which is then wrong for the current week, so I want to create a macro that can be clicked when the week is duplciated to clear all the content from (in the below example) cells C2:E8 and then an auto script to run that that will return the original formula if the cell text is deleted, however, I don't want it part of the macro as there could be mid-week changes where content is deleted, so I don't want the whole macro to be run clearing all data.

IE a simple example (the main file is a lot larger but as an example)

the names are generated based on team and station, however, Harry on Thursday is on leave so Clare has been manually typed in there. I want to be able to delete all the cell content for next week but the formulas to the auto add back in?

Thanks,

James

 

[offthelip]

shoud be easy enough if you tell us what the formulae are, otherwise we are just guessing!!

 

[jimbogarner]

sorry this is the formula in one of the cells, however, this will obviously change as the cell changes, IE D19 becomes D20 etc. etc.

=IFERROR(INDEX(Lists!$H$2:$S$100,MATCH($D19,Lists!$H$2:$H$100,0),MATCH(G$16,Lists!$H$1:$S$1,0))," ")

Thanks,

James

 

[offthelip]

I just record this macro using your equation and it seems to work. :

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C2').activate()
  .setFormula('=IFERROR(INDEX(Lists!$H$2:$S$100,MATCH($D19,Lists!$H$2:$H$100,0),MATCH(G$16,Lists!$H$1:$S$1,0))," ")');
  spreadsheet.getRange('D2:E2').activate();
  spreadsheet.getRange('C2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('C3:C8').activate();
  spreadsheet.getRange('C2:E2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};