archiving / copying all the values in column A to column C based on date

Question

archiving / copying all the values in column A to column C based on date. Also, if the date is change, a new set of values will be copied and the previous value won't be deleted.I was working on a daily schedule of every employee at the same time it will be recorded as their attendance based on the date. Can someone help me?

Example 1

Example 2 : if I change the date, the previous record won't get deleted and will copy a new set of values based on date

If I press the (save button) I want all the the values from column B2:B4 will be copied to column F2:F4 based on the date on column C1

The 2nd screenshot shows if I change the date and press again the (save button) the previous value won't get deleted and a new set of values will be copied based on the actual date on column C1

Answer 1

In order to solve your issue I suggest you to use Apps Script and write a script and use this function:

function dateFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var currDate = sheet.getRange("C1").getValue();
  var originRange = sheet.getRange("B2:B4");
  var headers = sheet.getDataRange().getValues()[0];
  var dateFound = false;

  for (var i=5; i<headers.length && !dateFound; i++) {
    if (currDate.toString() == headers[i].toString()) {
      originRange.copyTo(sheet.getRange(2, i + 1, 3));
      dateFound = true;
    }
  }

  if (!dateFound)
    SpreadsheetApp.getUi().alert("Date not found!");  
}

The script works by gathering all the dates from your headers and will try to see if there's a match with the date from the C1 cell. If a match has been found, the values will be copied correspondingly, otherwise, an alert prompt will show up specifying that the date has not been found.

Now, you will need to link this function to a button so it will only be executed when you click it.

You can easily do that by inserting a drawing and shape/draw it so it will resemble a button of your liking. You will have to Save and Close it.

Then, when you right click on it the option of Assign a script will appear and you have to put the name of the function from above in there, as shown below:

So now, every time you press the Save button, the script will be executed.

Furthermore, I suggest you read the following links since they might help you:

1. Apps Script Spreadsheet Service;

2. Sheet Class .getDataRange();

3. Range Class .copyTo().