How to use Google Apps Script to retrieve dates from cell values?

One common use case for Google Apps Script is to retrieve dates from cell values in Google Sheets. In this tutorial, we will explore some ways to accomplish this task using Google Apps Script.

Method 1: Using the built-in Date object

The first method to retrieve dates from cell values is to use the built-in Date object in JavaScript. This method involves converting the cell value to a date object, and then formatting it to your desired date format. Here’s an example code snippet:

// Get the active sheet
var sheet = SpreadsheetApp.getActiveSheet();

// Get the cell value as string
var cellValue = sheet.getRange("A1").getValue().toString();

// Convert the cell value to date object
var date = new Date(cellValue);
 
// Format the date object to "dd-mm-yyyy" format
var formattedDate = ("0" + date.getDate()).slice(-2) + "-" + ("0" + (date.getMonth() + 1)).slice(-2) + "-" + date.getFullYear();

// Print the formatted date
Logger.log(formattedDate);

In this example, we first get the active sheet and then retrieve the cell value in A1 as a string. We then convert this string to a date object using the `new Date()` constructor. Once we have a date object, we can format it in any desired format using various date methods provided by JavaScript. In this case, we format the date to `”dd-mm-yyyy”` format using the `getDate()`, `getMonth()` and `getFullYear()` methods.

Method 2: Using the moment.js library

The second method to retrieve dates from cell values is to use a popular JavaScript library called moment.js. This library provides a rich set of date manipulation and formatting functions that can make this task much simpler. Here’s an example code snippet:

// Get the active sheet
var sheet = SpreadsheetApp.getActiveSheet();

// Get the cell value as string
var cellValue = sheet.getRange("A1").getValue().toString();

// Convert the cell value to date object using moment.js
var date = moment(cellValue, "DD/MM/YYYY");
 
// Format the date using moment.js
var formattedDate = date.format("DD-MMM-YYYY");

// Print the formatted date
Logger.log(formattedDate);

In this example, we first get the active sheet and then retrieve the cell value in A1 as a string. We then use the `moment()` function provided by moment.js library to parse this string as a date object. Once we have a date object, we can format it in any desired format using various formatting functions provided by moment.js. In this case, we format the date to `”DD-MMM-YYYY”` format using the `format()` function.