How to professionaly format Google Spreadsheet Data using Google Apps Script?

We always have a situation where we need to format our data in a professional look. Although, we know how to do it manually and it takes a bit time. However, today's lession, we will learn how to format Google Spreadsheet Data using Google Apps Script (GAS) which looks clean and professional look and it is just a click away.

1. Copy the below code by clicking Copy on the top right corner.

              
                //Custom menu
                function onOpen() {
                  var ui = SpreadsheetApp.getUi();
                  ui.createMenu('Custom Menu')
                    .addItem('Format Me', 'formatMyData')
                    .addToUi();
                }

                function formatMyData() {
                  // Display a dialog box with a message and "Yes" and 
                  "No" buttons.
                  // The user can also close the dialog by clicking the 
                  close button in its title bar.
                  var ui = SpreadsheetApp.getUi();
                  var response = ui.alert('Did you select the cell where to start from?', 
                  ui.ButtonSet.YES_NO);

                  // Process the user's response.
                  if (response === ui.Button.YES) {
                    var spreadsheet = SpreadsheetApp.getActive();
                    spreadsheet.getCurrentCell().activate();
                    var currentCell = spreadsheet.getCurrentCell();
                    spreadsheet.getSelection()
                    .getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
                    currentCell.activateAsCurrentCell();
                    spreadsheet.getActiveRangeList().setFontWeight('bold');
                    currentCell = spreadsheet.getCurrentCell();
                    spreadsheet.getSelection()
                    .getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
                    currentCell.activateAsCurrentCell();
                    spreadsheet.getActiveRange()
                    .applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY);
                    var banding = spreadsheet.getActiveRange().getBandings()[0];
                    banding.setHeaderRowColor('#bdbdbd')
                      .setFirstRowColor('#ffffff')
                      .setSecondRowColor('#f3f3f3')
                      .setFooterRowColor('#dedede');
                    var sheet = spreadsheet.getActiveSheet();
                    sheet.getRange(1, 1, sheet.getMaxRows(), 
                    sheet.getMaxColumns()).activate();
                    spreadsheet.getActiveSheet().setHiddenGridlines(true);
                    spreadsheet.getActiveRangeList().setFontSize(9)
                      .setVerticalAlignment('middle')
                      .setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
                    spreadsheet.getActiveSheet().autoResizeColumns(1, 26);
                    spreadsheet.getCurrentCell().activate();
                  } else {
                    Logger.log(
                      'The user clicked "No" or the close button in the 
                      dialog\'s title bar.'
                    );
                  }
                };
              
            

2. Go to your Google Sheet and click on Extentions then Apps Script
3. Delete already existed function.

                  function myFunction() {
    
                  }
                

4. Paste the previously copied code to the Google Apps Script Editor.
5. Click on Save Project button.
6. Click on Run button

Now, Authorizatoin is required. A prompt dialog box will appear asking you: This project requires your permission to access your data. click on Review permissions button.

7. Choose an account to continue with this project.
8. Click on Advance button.
9. Click on Go to Untitled project (unsafe).
10. Click on Allow.
11. Go back to your sheet where you will find Custom Menu
12. Go to your dataset and select the Cell where you want to start your data to format.
13. Then head over to the Custom Menu and click on Format Me.

And, there you go!. Each time you need to format your data just repeat this process.