Delete Cells in sheets via script that is triggered from another sheet. [google sheet script]

Question

For a spreadsheet that contains several sheets, I have a script with which I can delete data in all sheets from certain ranges at the push of a button.

Now I have a main spreadsheet that is linked to several other spreadsheets (and the sheets they contain) (import range, queries, etc.).

Is it possible to delete data in certain ranges by script at the push of a button in the connected spreadsheets, whereby I would like to trigger this from the main spreadsheet?!

The data that is to be deleted from the sheets using the script is manual user input and therefore does not come from the main spreadsheet. The Ranges/Cells that should be deleted are different between Sheet 1.A and 1.B

Attached a graph for better understanding:

graph

My script so far is:

    function onOpen() {

  SpreadsheetApp.getUi()

    .createMenu('Delete User Input Data')

    .addItem(

      'Delete Data now',

      'userActionResetMultipleSheetsByRangesAddresses'

    )

      .addToUi();

}

function userActionResetMultipleSheetsByRangesAddresses() {

  var sheetNames = [

    { name: 'Sheet1.A', rangesAddressesList: ['Y3:AG99'] },

    { name: 'Sheet1.B', rangesAddressesList: ['AI3:AX99'] },

    ];

  sheetNames.forEach(function(sn) {

    var sheet = SpreadsheetApp.getActive().getSheetByName(sn.name);

    if (sheet) {

      resetByRangesList_(sheet, sn.rangesAddressesList);

    }

  });

}

function resetByRangesList_(sheet, rangesAddressesList) {

  sheet.getRangeList(rangesAddressesList).clearContent();

}

Just asking. The code is at a level where this question should never have come up. Do you understand the code or could you modify the code? If not consider taking a JavaScript tutorial. You just need spreadsheet ids and add SpreadsheetApp.openById() for each id and loop through. Consult the official documentation. — TheMaster

Cooper Cooper · Accepted Answer · 2020-02-27T22:04:07

I think you could use something like this:

function userActionResetMultipleSheetsByRangesAddresses() {
  var ss=[{id:'',name:'Sheet1.A', rangesAddressesList: ['Y3:AG99'] },{id:'',name:'Sheet1.B',rangesAddressesList: ['AI3:AX99'] },
          {id:'',name:'Sheet2.A', rangesAddressesList: ['Y3:AG99'] },{id:'',name:'Sheet2.B',rangesAddressesList: ['AI3:AX99'] },
          {id:'',name:'Sheet3.A', rangesAddressesList: ['Y3:AG99'] },{id:'',name:'Sheet3.B',rangesAddressesList: ['AI3:AX99'] }
         ];
  ss.forEach(function(e) {
    var sheet = SpreadsheetApp.openById(e.id).getSheetByName(e.name);
    if(sheet){
      sheet.getRangeList(e.rangesAddressesList).clearContent();
    }
  });
}

You will just have to add the spreadsheet ids and correct the rest of the sheet names and ranges.

Delete Cells in sheets via script that is triggered from another sheet. [google sheet script]

1 Answers