I have a workbook (Generator) that pulls Google form response data from another workbook (Responses). Originally the form data went to sheets that were in the Generator workbook. However, to simplify the Generator workbook and speed up other script files, I now have the form responses going to their own separate workbook (Responses). That data needs to be "refreshed" for each user, which originally was done using UI button that triggered the .deleteRows when the worksheets were all in the same workbook. However, now that they're in separate workbooks, I'm not sure how to get the .deleteRows to reference the new workbook (Responses). I've put my original code below, which works exactly as I need it to when all the sheets are in the same workbook. The clearContent functions don't need to be touched, those are referencing the sheet with additional UI data and the sheet where the script is triggered. If anyone can point me in the right direction for how to get it to reference the new workbook (Responses) I'd really appreciate it. Thanks

function clearRange() {
  var ss = SpreadsheetApp.getActive();
  var Trench = ss.getSheetByName("Trenching");
  var Pressure = ss.getSheetByName("Pressure");
  var Falls = ss.getSheetByName("Falls");
  var Chemical = ss.getSheetByName("Chemical");
  var Confined = ss.getSheetByName("Confined");
  var LOTO = ss.getSheetByName("LOTO");
  var Rigging = ss.getSheetByName("Rigging");
  var Heavy = ss.getSheetByName("Heavy");
  var Hot= ss.getSheetByName("Hot");
  var Pipe= ss.getSheetByName("Pipe")

  Trench.deleteRows(2, 5);
  Pressure.deleteRows(2, 5);
  Falls.deleteRows(2, 5);
  Chemical.deleteRows(2, 5);
  Confined.deleteRows(2, 5);
  LOTO.deleteRows(2, 5);
  Rigging.deleteRows(2, 5);
  Heavy.deleteRows(2, 5);
  Hot.deleteRows(2,5);
  Pipe.deleteRows(2,5);

 var data1 = SpreadsheetApp.getActive().getRange("B3:B6").clearContent()
 var data2 = SpreadsheetApp.getActive().getRange("J3:J7").clearContent()
 var data3 = SpreadsheetApp.getActive().getRange("J9").clearContent()
 var data4 = SpreadsheetApp.getActive().getRange("E8:F10").clearContent()
 var data5 = SpreadsheetApp.getActive().getRange("A13:A133").clearContent()
 var data6 = SpreadsheetApp.getActive().getRange("G13:G136").clearContent()
 var data7 = SpreadsheetApp.getActive().getRange("I13:I136").clearContent()
 var data8 = SpreadsheetApp.getActive().getRange("K13:K136").clearContent()
 var data6 = SpreadsheetApp.getActive().getRange("L13:L136").clearContent()

}

CodePudding user response：

Clear ranges from another Spreadsheet

function clearAnotherSpreadsheet(id="11yNxdh_GIokeHIctdzt3LDKVm7rYIJvAPlMLzunn7zE") {
  const ss = SpreadsheetApp.openById(id);
  const sh = ss.getActiveSheet();
  const rl = sh.getRangeList(["B3:B6", "J3:J7", "J9", "E8:F10", "A13:A133", "G13:G136", "I13:I136", "K13:K136", "L13:L136"]);
  rl.getRanges().forEach(rg => rg.clearContent())
}