Returning correct date format from forEach

[RobertB13]

I've written a script to execute a mail merge.

I am trying to figure out how to get the return value to be the same as on the original source spreadsheet.

Spreadsheet = Sat, 11/02/19 9:00

Mail Merge result : Sat Nov 02 2019 09:00:00 GMT-0700 (PDT)

---------

I'm a super nube so here is the code....HELP!

function myFunction() {

  var docScoreTemplateID = "1cRXsEAaN4aeIFGfk-HpJ-5faU1zDiIwo3aXw5PlFp";

  var docScoreMergeID = "1OvlDGa0kJOseCoANksLDgQnrAf9hyQ6kh6UgVHJBl";

  var wsID = "1tGnvrAEPdnzMyv6F2yl_1m9j-AoaIILy8TEyE_F77";

  var docScoreTemplate = DocumentApp.openById(docScoreTemplateID);

  var docScoreMerge = DocumentApp.openById(docScoreMergeID);

  var ws = SpreadsheetApp.openById(wsID).getSheetByName("Division_Sort");

 

  var data = ws.getRange(2,1,ws.getLastRow()-1,9).getValues();

  

  var templateParagraph = docScoreTemplate.getBody().getParagraphs();

 

  docScoreMerge.getBody().clear();

  

  data.forEach(function(r){

      createMailMerge(r[3],r[4],r[5],r[2],r[0],r[7],r[8],templateParagraph,docScoreMerge);

  });

  

}

function createMailMerge(umpire,division,game,field,gametime,team01,team02,templateParagraph,docScoreMerge){

  templateParagraph.forEach(function(p){

    docScoreMerge.getBody().appendParagraph(

      p

      .copy()

      .replaceText("{umpire}", umpire)

      .replaceText("{division}", division)

      .replaceText("{game}", game)

      .replaceText("{field}", field)

      .replaceText("{gametime}", gametime)

      .replaceText("{team01}", team01)

      .replaceText("{team02}", team02)

    );                       

  });

  docScoreMerge.getBody().appendPageBreak();

}

[Script GS]

You can either make use of getDisplayValue() or Utilities.formatDate

- Sourabh

[RobertB13]

I am totally new to scripting. 

Can you show me exactly how to encorporate that into the code?
I can't seem to figure out how to assign the formatting to the r[0] which contains the value being copied to the google doc.

[RobertB13]

Hey Sourabh,

Thanks for the reply. Is there any way you can show me exactly how and where to place that into my code for formatting. I’ve tried myself for hours with no success.

Much appreciate your help!!

[Yasir Karam]

Hi, Am facing issues when applying this custom function countColoredCells which counts cells in specified range on cell background color. Does this function missing something? I get out of range error.

function countColoredCells(countRange,colorRef) {

  var activeRange = SpreadsheetApp.getActiveRange();

  var activeSheet = activeRange.getSheet();

  var formula = activeRange.getFormula();

  

  var rangeA1Notation = formula.match(/\((.*)\,/).pop().trim();

  var range = activeSheet.getRange(rangeA1Notation);

  var bg = range.getBackgrounds();

  var values = range.getValues();

  

  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop().trim();

  var colorCell = activeSheet.getRange(colorCellA1Notation);

  var color = colorCell.getBackground();

  

  var count = 0;

  

  for(var i=0;i<bg.length;i++)

    for(var j=0;j<bg[0].length;j++)

      if( bg[i][j] == color )

        count=count+1;

  return count;

};

Yasir Karam about.me/karamyasir

--
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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e00c0273-8235-4e30-b781-c7c57980845c%40googlegroups.com.