0

I am concatenating data from two columns and writing in another column. My Col18 has dates and after concatenation, script changes the format. How can I force it to not change the MM/DD/YYYY format as I want it to display in the exact format.

//Concatenates data from Col 2 & Col 18 and posts the result to Col 1 

function addCol() {
  var ss = SpreadsheetApp.openById(SpreadsheetID);
  var sheet = ss.getSheetByName(SheetName);

  sheet.insertColumns(1,1);

  var lr = sheet.getLastRow();
  var col2 = sheet.getRange(4, 2, lr,1).getValues();  //Get values from Col 2 starting row 4
  var col18 = sheet.getRange(4, 18, lr,1).getValues(); //Get values from Col 18 starting row 4
  
  var results = [];        //Empty array for concatenated elements

  //Add items to results after concatenation
  for(var i=0; i<lr; i++){
    results[i] = [col2[i][0]+"_"+col18[i][0]];
  }

  //Write results to Column 1 starting on row 4
  sheet.getRange(4, 1, lr, 1).setValues(results);

}

Image of the data, current output and the output that I am trying to get Image

I tried reading up the following but could not get my head around it.

https://developers.google.com/google-ads/scripts/docs/features/dates

RF919
  • 47
  • 4
  • 1
    Use `.getDisplayValues()` instead of ``getValues()`` – TheMaster Jun 22 '22 at 20:18
  • @TheMaster .getDisplayValues() makes the output North_6/1/2022, how can I force it to get North_06/01/2022 – RF919 Jun 23 '22 at 00:29
  • You can't. Use ``getValue`` and `Utilties.formatDate` as mentioned in the linked duplicate [above](https://stackoverflow.com/a/17727300) – TheMaster Jun 23 '22 at 08:22

0 Answers0