If name is in a column, turn the column into a date format Google sheets

user_14388939 0
Viewed 94

Context

I want to dynamically change the data format in the respective columns of the below to a proper date format: I used this code but now want to make it dynamic. The original number formats in the date columns are date/time formats from google finance. But I just want the format in dd/mm/yyyy.

enter image description here

This was my original code:

function Dateformats() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Within the values copied spreadsheet, columns that have 'Date' will be the subject of this function
  var sheet = spreadsheet.getSheetByName('Values (Live Data copied)')
 // var sheet = spreadsheet.getSheetByName("Values (Live Data copied)")
  var dataList = sheet.getRangeList(['A:A', 'C:C', 'E:E', 'G:G', 'I:I', 'K:K', 'M:M', 'O:O']);
    dataList.setNumberFormat('dd/MM/yyyy');

}

New code for dynamic results

  function Dateformats() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // Within the values copied spreadsheet, columns that have 'Date' will be the subject of this function
  var range = spreadsheet.getRangeByName('Values (Live Data copied)!Date')
 
  if(range !== null){
    range.setNumberFormat('dd/MM/yyyy');
  }
}

Both work though the latter does not actually show up on the sheet. The date formats still remain unchanged.

Problem

Is anyone aware of a fix for the dynamic reformating of columns that have 'Dates' in them?

google-apps-script google-sheets javascript
1 Answers

Explanation:

Your goal is to identify whether a column contains a date object. If it contains a date object, you want to change the format of the date from timestamp to a date.

The following script:

  • will determine the type of each column based on the 4th row and it will return true if the type is date or false otherwise. I use map to check for date elements based on this condition:

    typeof em.getMonth === 'function'

  • forEach column of type date set the format of the whole column.

Solution:

function Dateformats() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName('Values (Live Data copied)')
  const checkTypes = sheet.getRange(4,1,1,sheet.getLastColumn()).
                   getValues().
                   flat().
                   map(em=>typeof em.getMonth === 'function');
  checkTypes.forEach((ct,i)=>{
        if(ct){
           sheet.getRange(4,i+1,sheet.getMaxRows(),1).
           setNumberFormat('dd/MM/yyyy');
        }
  });
}

Please note again that I am using the 4th row to determine the type of each column. Therefore, this row should contain the same type as the rest of the cells in the same column. Namely, the solution won't work if a column contains multiple types of data.

user_112252910
Related