Need to Center and Word Wrap in Google Sheet for an Intake Form for an Log Sheet

[Warren Anderson]

Good morning - 

I am a HR Specialist at a US company. My job is to track employees who need to go out on various types of leaves.  Because of the large population that I have to manually research and their work information and types of leave, I created an automated leave log that will input information that would save time & ensure proper data collection. 

The Leave Log has  three (3) main tabs:  

The INTAKE FORM - This is where employee leave request is entered 

LEAVE LOG  - This is where employee leave requested is saved to

LEAVE LOG CENSUS - This is the database where all employee's work information is stored

I have designated cells on the tab titled "INTAKE FORM" that will populate once an employee's name appears in the cell "D3."  The data is pulled using Apps Script from another tab in the workbook titled "Employee Census."

Once additional information is entered on the "INTAKE FORM," the data is then saved on another tab titled " LEAVE LOG."

Problem:  When the employee's data is retrieved from either the Leave Log Census or the Leave Log tabs, the data is not centered & word wrapped to fit the designated cells on the Intake Form.

I would like to have a Macro to Word Wrap and Center the retrieved data to fit each designated cell on the Intake Tab.

Attached you will find the Apps Script in Notes.  I have also attached a screenshot of the Intake Form to illustrate the problem.

Thank you for your help!!

[DimuDesigns]

Have you tried manually updating the formatting of the individual cells highlighted? If the code is just updating the value of the cells and not changing the formatting of those cells, then using a macro to update formatting that is static seems redundant. 

[AliceKeeler Keeler]

I asked Gemini, it's too early for me to debug,  I hope this works: 
function formatIntakeForm() {
  // Get the spreadsheet and the "INTAKE FORM" sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("INTAKE FORM");

  // Define the range of cells to format
  var range = sheet.getRangeList([
    'D5:D11', 'F5:F11', 'H5:H11', 'J5:J11',
    'D13:D19', 'F13:F19', 'H13:H19', 'J13:J19',
    'D21:D39', 'F21:F39', 'H21:H39', 'J21:J39'
  ]).getRanges();

  // Loop through each range in the range list
  for (var i = 0; i < range.length; i++) {
    // Apply word wrap and center alignment
    range[i].setWrap(true)
           .setHorizontalAlignment("center")
           .setVerticalAlignment("middle");

[Keith Andersen]

The problem is you are using clear() to clear the form data. You should be using clearContent() . Clear wipes out the data and formatting, whereas clear content only removes the data within the cell.

Change those and you will not have to worry about formatting again.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/4ace291c-522c-4fe4-b1cc-75e6c2559263n%40googlegroups.com.

[Keith Andersen]

Alice,
Just FYI. Gemini is awful. It is wrong more often than it is right. It has given me deprecated code and code that is non-functional.

Claude AI is awesome. It's never let me down.

FYI
Keith

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Shawna Kovach]

Do you like Claude more than ChatGPT?

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BF6nVxDcJwN223cJ8As2jiMixh4dmP3EAPrpJpHiADivQ%40mail.gmail.com.

[Keith Andersen]

Yes. Claude is, as far as I've seen, the leader in AI coding.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAKDeTYfE_7JP8oGZxRRZWmWgmCkh%3Drbb56TB5HWhNFAvdRNQtg%40mail.gmail.com.

[Michael O'Shaughnessy]

I will jump on the Claude bandwagon here for coding.

I get very good results when I use it. 

Gemini, ChatGPT, Perplexity.... great for "revising text" or "explain nuclear fission as if I am a 5th grader" but not so much for coding.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BEuGYreo4rHbv57WQSZ8WA1%2BLZX%3DFr%2BFK2MudzK19yM_A%40mail.gmail.com.

[Shawna Kovach]

Thank you so much for your feedback!!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLgq-a8Jc25-pDWDTknjX_6pKZ62bt_UdY9bBTrfp74XwA%40mail.gmail.com.

[Keith Andersen]

Michael,
Amen. I don't consider myself anything more than a hobbyist coder with regard to apps script but every time I use Gemini it gives me wrong code. And that's pretty bad when somebody is low on the coding spectrum as I am, can tell that Gemini is just giving bad information. I can't tell you how many times it has written code for me and given me deprecated code or JavaScript that's not supported by apps script. Gemini has infuriated me more times than one and I don't even consult it anymore.

Claude - on the money every time.

I recently asked Claude for best practices with regard to apps script lock service and he gave me a really fantastic lock wrapper - which I put here on the community for review. Works great!.

Cheers
Keith

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLgq-a8Jc25-pDWDTknjX_6pKZ62bt_UdY9bBTrfp74XwA%40mail.gmail.com.