How to create a button which clears data in spread

问题:

Currently I have a google spreadsheet document in which users are inputting data based on conditional formatting the cells are changing colors. I would like to create a button with which I can easy restore all the cells into their first state.

I have this script at the moment:

function clearRange() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
 sheet.getRange('B13:B30').clearContent();
}

Which works like a charm but the thing is that I have default data in the cells and when this scripts get applied it clears the default data.

Any ideas how to make such a script which will clear only the inputted data by the user.

回答1:

It's not the solution you want but apart from what Scott suggested, which is the most straightforward approach, you could make a copy of your sheet in the unedited state. In this copy have a script which will just copy and paste the info into the sheet that the user fills in.

Then if you make changes you just need to update the template sheet and not the script.

This is a very basic script but should you'll get the idea.

function myFunction() {

  var app = SpreadsheetApp;

  var tempSheet = app.getActiveSpreadsheet().getSheetByName('Sheet1');
  var inputSheet = app.openById('-----ID OF INPUT SHEET----').getSheetByName('Sheet1');

  var tempData = tempSheet.getDataRange().getValues();

    inputSheet.getRange(1, 1, tempData.length, 
    tempData[1].length).setValues(tempData);
}