How can I restore a specific version of my google sheet by using a google script?

Marco

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!

Brett Grear

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.

edited at
0

Comments

0 comments
Login to comment

Related

How to read data from a specific version of Google Sheet?

How can I write the data in Firestore to Google Sheet with app script?

How can I get the folder ID of a folder in my drive using google script?

How can I parse an URL's HTML and insert data to a Google Sheet using Google Script?

How do i set timestamp to specific cells everytime i open my google sheet?

How can I assign a Google Sheet script to only one sheet?

Google Script, how can I copy a row from Sheet1 to Sheet2 instead of just one cell by using regex?

How do I make my script target a specific column in Google Sheet?

How can I tell google script where to print in sheet?

How could I get the link of an specific sheet with Google script?

How do I automatically archive a google sheet with a google script?

Running script to a specific sheet - Google sheet

In Google Sheets how can I write a script to move a drawing to a specific location on the sheet?

How can I rearrange a Google Sheet using the data within the sheet?

How can I make a 'google sheet script' or formula to change values in different cells based on a checkbox in the sheet

How do I create a conditional format for my google sheet using only google scripting?

How do I get arrays mapped in a row in google sheet using JavaScript/Google App script

Using Google Apps Script, how can I replace text in a Google Sheets template to make a new Sheet?

How can I optimise this code for my Google sheet macro?

How can I fetch Google Sheet name from Google sheet ID using python and google sheets API?

How can I fix the range of a Google Sheet MATCH function when using a move rows apps script?

How Can I add comment on a cell using google sheet app script

How do I get 5 rows from the end of my sheet in Google Sheets using Apps Script?

How can I force my program to use an updated version of Google-Chrome when using it in headless mode?

How to import specific text/line under headings in Google doc to Google sheet using google apps script?

How can I move urls from sheet ranges while building the links at destinations using Google Apps Script?

Script to sort, export sheet as PDF, and save it to a specific folder in my Google Drive [Google Sheets] [Apps Script]

Using Apps Script, how can I display a google doc checklist from information in a google sheet?

How can I redirect back to my website after google apps script sheet submission?