Google Sheets - Fill in date from another cell if empty

Question

Thanks in advance for your help! Our team has a Google Sheet that is used to manage projects.

There is a Start Date (Column E) and a **Due Date **(Column F) that can be set for any task, however some require it, some don't. For example, for call tasks, we put the same start/end date. We need both cells to be filled in so we can display a gantt chart with all activities.

Is there a way to automatically fill in the Start Date cell with the End date (and vice versa), if one of them is empty?

I didn't want to populate those cells with a formula to keep things simple an played around with the formulas in conditional formatting but couldn't find a way!

Answer 1

This code is not very elegant (I'm still pretty new), but this may work if you are just looking to add a start date where there is an end date and vice versa.

EDIT: Using your implementation plan, I was able to get a version of this script to work. There was an issue where the startDate and endDate arrays held time data (ex: 4/21/2022, 12:00 PM). I was able to work around this by splitting the element from the comma so that it would only show the date.

function FillDate() {
let sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('YOUR_SHEET_NAME');
let dateColumns = sheet.getRange(13,4,sheet.getLastRow(),2).getValues();
let startDate = dateColumns.map(function(r){return r[0].toLocaleString();});
let endDate = dateColumns.map(function(r){return r[1].toLocaleString();});
let headerRows = 13; //you may want to change this if you have more than one header row


for (i=0; i<sheet.getMaxRows(); i++)
{


if (((startDate[i] != '') && (endDate[i] != ''))||((startDate[i] === '') && (endDate[i] === ''))) 
{
  //This is empty because you don't want to do anything if both Start and End Dates are empty or full.
}
else 
{
  if((startDate[i] == '') && (endDate[i] != ''))
  {
    let splitEndDate = endDate[i].split(",");
    sheet.getRange(headerRows+i,4).setValue(splitEndDate[0]);
  }
      else 
      {
        let splitStartDate = startDate[i].split(",");
        sheet.getRange(headerRows+i,5).setValue(splitStartDate[0]);
      }
}
}
}