gScript: setvalue to multiple sheets

Question

Trying to take a user's prompted response and setting the value of a range of cells with that prompted response. I have it working on sheet [0] using the code below.

function CopyToColumnE() {
  //Get current spreadsheet and sheet0
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = source.getSheets()[0];


  //Prompt for the data to fill into the range
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Enter the value to copy to all Column E cells');
  var value = response.getResponseText();

  //Set the range of cells
  var range37 = sheet.getRange("E3:E7");
  var range1115 = sheet.getRange("E11:E15");
  var range1923 = sheet.getRange("E19:E23");

  // Call the setValue method on range and pass in our promoted response
  range37.setValue(value);
  range1115.setValue(value);
  range1923.setValue(value);
}

Now...how can I set the same range across ALL sheets? Not just sheet[0], ALL sheets. The user who will use this script may add/remove sheets on a whim.

So, prompt user for 'value' and set sheet0.ranges, sheet1.ranges, sheet2.ranges...etc

EDIT After Karl_S's helpful hint I ended up with this code and it works!

function CopyToColumnE() {
  //Get current spreadsheet and sheet
  var source = SpreadsheetApp.getActiveSpreadsheet();
  //Prompt for the data to fill into the range
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Enter the value to copy to all Column E cells');
  var value = response.getResponseText();
  //Loop through ALL sheets in spreadsheet/workbook
  var allsheets = source.getSheets();
    for (var s in allsheets){
    var sheet=allsheets[s]



  //Set the range of cells
  var range37 = sheet.getRange("E3:E7");
  var range1115 = sheet.getRange("E11:E15");
  var range1923 = sheet.getRange("E19:E23");

  // Call the setValue method on range and pass in our promoted response
  range37.setValue(value);
  range1115.setValue(value);
  range1923.setValue(value);
}
}

Karl_S Karl_S · Accepted Answer · 2017-03-08T15:18:15

.getSheets() will get all the sheets, so try reading them all and looping through each one

var allSheets = source.getSheets();

for (i in allSheets) {
  sheet = allSheets[i];
  // As a sample, this will give you the sheet name for the current sheet:
  var mayName = allSheets[i].getName();

  //  Add your code to add the data to sheet inside the loop
}

gScript: setvalue to multiple sheets

1 Answers