Update Select Tabs in Google Sheets Based on User Selection

hunter21188 0
Viewed 26

I have a single Google Sheet with multiple tabs (all named like the following: "PP01", "PP02", etc through "PP26"). I need the user to be able to update the tabs starting at a certain one and updating all tabs from their selection through "PP26".

They would select "Update Sheets" from a custom dropdown and it would prompt them to select what tab they want the start the updates (selecting the tab from a dropdown would be best). So lets say they select PP10...it would update the tabs from PP10 through PP26 and nothing before PP10. Not really sure how to go about this. Any ideas? Thanks!

google-sheets google-apps-script
1 Answers

You could use a data validation dropdown as I've shown in my test sheet. The drop down is linked to the range of values in column A. When the user picks a value from the drop down the onEdit(e) will loop through the sheet names from whatever number is picked to PP26.

enter image description here

Code.gs

function onEdit(e) {
  try {
    if( e.range.getSheet().getName() === "TestData" ) {
      if( e.range.getColumn() === 2 ) {
        if( e.range.getRow() === 1 ) {
          if( ( e.value !== undefined ) && ( e.value !== "") ) {
            let index = parseInt(e.value.match(/\d+/));
            let result = ""
            for( let i=index; i<27; i++ ) {
              result = result.concat("PP"+i.toString().padStart(2,0),"\n");
            }
            e.range.setValue("");
            SpreadsheetApp.getUi().alert(result);
          }
        }
      }
    }
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}
TheWizEd0
Related