Deleting specific data from multiple Google Sheets

Question

I am trying to clear data from multiple sheets. I have a code that works for the current active sheet and data ranges I need, but I can not for the life of me figure out how to make it clear the same data from multiple sheets. Here is the code:

// This section adds the top menu
   function onOpen() {
     SpreadsheetApp.getUi().createMenu('Reset')
      .addItem("Formulas Only", "ResetFormulas")
      .addItem("Entire Page", "ResetPage")
      .addToUi()
}

// This section makes a button to reset the whole page copies the entire page
   function ResetPage() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheetByName('Data1');  // Enter tab name of what you’re copying from
     sheet.getRange('A1:U39').copyTo(ss.getRange('A1:U39')) //enter entire tab range

}
// This section copies only the cells you specify
   function ResetFormulas() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Sheet12'); // Enter tab name here of what you’re copying 
from
//Header
  sheet.getRange('B6:E20').copyTo(ss.getRange('B6:E20')) //first range you’re copying
  sheet.getRange('N6:Q20').copyTo(ss.getRange('N6:Q20')) //next range
  sheet.getRange('B25:E39').copyTo(ss.getRange('B25:E39')) //etc
  sheet.getRange('N25:Q39').copyTo(ss.getRange('N25:Q39')) //you get it

 }

What specifically would I change to make it clear multiple sheets rather than the active one?

EDIT:Solved I figured it out... at least a way that accomplishes what I am after. If anyone else is looking to do the same thing here is the code that did it. I ONLY use the ResetFormulas part of the function not the ResetPage. This only works if you have the same exact sheet multiple times that you want to clear specific data from. You will need a protected master sheet that you label in the script, mine was "Sheet 12". This is what will be copied and will replace the ranges you specify from each sheet that you specify. I don't know if everything is needed or if there is an easier way, I don't know anything about scripts or code, this is from hours of looking at other examples and just typing stuff till it worked.

// This section adds the top menu
   function onOpen() {
    SpreadsheetApp.getUi().createMenu('Reset')
        .addItem("Formulas Only", "ResetFormulas")
        .addItem("Entire Page", "ResetPage")
        .addToUi()
}

// This section makes a button to reset the whole page copies the entire page
function ResetPage() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName('');  // Enter tab name of what you’re copying from
   sheet.getRange('').copyTo(ss.getRange('')) //enter entire tab range

}
// This section copies only the cells you specify
   function ResetFormulas() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheetByName('Sheet 12'); // Enter tab name here of where you are copying from.

 //The sheet,getRange(cellsyouwantcopied) copies the cells you want from your master sheet. The copyTo(ss.getsheetByName('Yoursheetnamehere').getRange(cellstobecopied) is where you type the sheet name and same range you want to be copied over

    sheet.getRange('B6:E21').copyTo(ss.getSheetByName('Sign Pricing').getRange('B6:E21')) 
  sheet.getRange('N6:Q21').copyTo(ss.getSheetByName('Sign Pricing').getRange('N6:Q21')) 
  sheet.getRange('B25:E40').copyTo(ss.getSheetByName('Sign Pricing').getRange('B25:E40')) 
  sheet.getRange('N25:Q40').copyTo(ss.getSheetByName('Sign Pricing').getRange('N25:Q40'))
  sheet.getRange('B44:E59').copyTo(ss.getSheetByName('Sign Pricing').getRange('B44:E59'))
  sheet.getRange('P53').copyTo(ss.getSheetByName('Sign Pricing').getRange('P53'))
}

Repeat the section "sheet.getRange().copyTo..." for as many sheets or ranges that you need cleared.

Can I ask you about the relationship between your script in your question and your goal of I am trying to clear data from multiple sheets.? — Tanaike
So I am building a pricing sheet. It will be the same exact sheet for every tab I end up having 5-10 tabs. That current script I am only using for the clear formulas function part of it. It currently clears designated cells on my active sheet. What i want is that code to clear data from the same exact designated cells on every tab at the same time not just the current active tab. — Jeddie
I do not see any code that "clears" data. Maybe what you mean y clearing is overwriting the data with the request sheet.getRange('A1:U39').copyTo(ss.getRange('A1:U39'))? If so, please see my answer below. — ziganotschka
Correct! Sorry yeah it does not clear but copies from another sheet. By using the code below, the 'Data1' is where it copies from, but by setting the other sheets via "sheet2" "sheet3", doesn't that just copy from those as well? I guess what I am asking is what sets my sheet that it will copy from and where do I list the ones I want to clear? I did attempt the code below but it still only clear my first sheet and no others. — Jeddie
I figured it out... at least a way that accomplishes what I am after. Anyone else looking to do the same thing here is the code that did it. — Jeddie

ziganotschka ziganotschka · Accepted Answer · 2021-02-15T10:35:23

The line sheet.getRange('A1:U39').copyTo(ss.getRange('A1:U39')) specifies that data shall be copied to the range 'A1:U39' in the active spreadsheet
It doe not specify in which sheet the range is located, this is why Apps Script assumes that you want to copy into the active sheet
If you want to chose instead a different sheet - you need to specify the sheet

Sample:

   function ResetPage() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var sheet = ss.getSheetByName('Data1');  // Enter tab name of what you’re copying from
     var sheet2 = ss.getSheetByName('Data2');  // Enter tab name of what you’re copying from
     sheet.getRange('A1:U39').copyTo(sheet2.getRange('A1:U39')) //enter entire tab range
  }

Deleting specific data from multiple Google Sheets

1 Answers