I would like to have a button that restore a specific version of my google sheet when I press it by using a google script.
The reason is that I would like to clear my google sheet but to set up the same format and everything as the original version in case someone else changes the format of the cells.
I though I could add a button and restore the version to the original before some enters any data.
Any idea about how to do this by using a script? (I'm open to suggestions)
Thank you!
I don't think that you can restore a version using a Google Script as the process of running a script updates the current version anyway.
You could write a script that cleared all of the editable cells (and set any formatting back to default) and add this to a custom menu so that you could reset the sheet at the press of a button.
This might achieve the same result you are looking for.
Here is one I have used before but it is obviously specific to my needs.
function clear() {
SpreadsheetApp.getActiveSpreadsheet().toast('Deleting');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
range.clear();
sheet.getRange(1,1).setValue('Copy and paste Data here')
.setBackground('yellow');
var maxRows = sheet.getMaxRows();
var maxColumns = sheet.getMaxColumns();
sheet.setFrozenRows(0);
sheet.deleteColumns(2,maxColumns-1);
sheet.deleteRows(2,maxRows-1);
sheet.setColumnWidth(1, 100)
SpreadsheetApp.getActiveSpreadsheet().toast('Deleted');
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Custom Menu')
.addItem('Clear','clear')
.addToUi();
}
A more specific example for you might be:
function clear() {
SpreadsheetApp.getActiveSpreadsheet().toast('Deleting');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
sheet.getRange('A26:E30').clearContent();
sheet.getRange('A33:E37').clearContent();
sheet.getRange('A40:E44').clearContent();
sheet.getRange('D2:D3').clearContent();
sheet.getRange('D5:D9').clearContent();
sheet.getRange('D12:D16').clearContent();
sheet.getRange('D18').clearContent();
sheet.getRange('A19').setValue(false);
sheet.getRange('C19').setValue(false);
}
I may have missed some sections so you will need to adapt for you usage. If you want to use the clear button that you have already created then you can right click it and assign the clear function to it so that every time you click clear it will execute the above script. This may not be the most elegant solution but it should work for your needs.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments