0

I would like to format a range of values into time into a single line of code. 

var C1GD29 = Utilities.formatDate(ReportSheet.getRange('D29').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD30 = Utilities.formatDate(ReportSheet.getRange('D30').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD31 = Utilities.formatDate(ReportSheet.getRange('D31').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD32 = Utilities.formatDate(ReportSheet.getRange('D32').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD33 = Utilities.formatDate(ReportSheet.getRange('D33').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD34 = Utilities.formatDate(ReportSheet.getRange('D34').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD35 = Utilities.formatDate(ReportSheet.getRange('D35').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD36 = Utilities.formatDate(ReportSheet.getRange('D36').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD37 = Utilities.formatDate(ReportSheet.getRange('D37').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD38 = Utilities.formatDate(ReportSheet.getRange('D38').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD39 = Utilities.formatDate(ReportSheet.getRange('D39').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");
var C1GD40 = Utilities.formatDate(ReportSheet.getRange('D40').getValue(), ss.getSpreadsheetTimeZone(), "HH:MM");

I have tried this below but I get error.

var TimeLog = Utilities.formatDate(ReportSheet.getRange('D29:D40').getValues(), ss.getSpreadsheetTimeZone(), "HH:MM");

Can someone show me the right way of writing it?

Norvin
  • 27
  • 5
  • Does this answer your question? [Loop through an array in JavaScript](https://stackoverflow.com/questions/3010840/loop-through-an-array-in-javascript) – TheMaster Nov 05 '19 at 14:55

1 Answers1

2
function formatDates() {
  var ss=SpreadsheetApp.getActive();
  var ReportSheet=ss.getSheetByName('ReportSheet');
  var rg=ReportSheet.getRange('D29:D40');
  var vA=rg.getValues().map(function(r){return r[0];});
  var vD=[];
  vA.forEach(function(e){vD.push([Utilities.formatDate(new Date(e), ss.getSpreadsheetTimeZone(), "HH:mm")])});
  rg.setValues(vD);
}

Simple Date Format

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • I figured if I could get range then get values first then I add in the format body.replaceText('1GD06', Utilities.formatDate(OneGradeData[5][3], ss.getSpreadsheetTimeZone(), "HH:MM")); but this returns an error. – Norvin Nov 05 '19 at 15:25
  • "HH:MM" is an incorrect format it stands for hours and months. I think you mean "HH:mm" which is hours and minutes. – Cooper Nov 05 '19 at 15:28
  • body.replaceText is a Document method not a Spreadsheet Method – Cooper Nov 05 '19 at 15:30
  • Oh yes I meant HH:mm. But I dont get error if I write it this way ` body.replaceText('1GD29', Utilities.formatDate(ReportSheet.getRange('D29').getValue(), ss.getSpreadsheetTimeZone(), "HH:mm"));` – Norvin Nov 05 '19 at 15:59