Including the worksheet title in the spreadsheet

问题:

I have a daily worksheet titled 07.09.17 Thursday, there are 4 sheets inside that I would like to add 07.09.17 (or the day of the sheet) in cell A1? Obviously every day is dated accordingly but when running a back up script I want it to refer to cell A1 as the date to be entered into the archive sheet. I have everything else working except this. I can't us =Now() or =Today() as I make the sheets up to 3 months in advance as we have some bookings that far in advance, also they are not only accessed on that day but many days.However the day of the backup is the date of the sheet.With many staff I don't want to have to trust them to do (ctrl) +; to insert date for each sheet manually. Thank you for your help.

    function onOpen() { // This function adds a custom menu to the spreadsheet (Backup to archive) so you can run the script from there.
var ui = SpreadsheetApp.getUi();
    ui.createMenu('Backup to archive')
    .addItem('timeStamp','dataBackup')
    .addToUi();
    }

function timeStamp() {
  SpreadsheetApp.getActiveSheet()
     var sheetNames = ['AM trip', 'PM trip', 'Pool / Beach', 'Night Dive'];
    .setActiveCell()
    .setValue(new Date());
}

  function dataBackup() {
  var inputSS = SpreadsheetApp.getActiveSpreadsheet();
  var archiveSS = SpreadsheetApp.openById('146WU8RghfFqlCpCSX7n6kBAKOyxcpVKt14yhVfvYz-g');
  var user = Session.getActiveUser().getEmail();
  var sheetNames = ['AM trip', 'PM trip', 'Pool / Beach', 'Night Dive'];
  for (var i = 0; i < sheetNames.length; i++) {

    var inputSheet = inputSS.getSheetByName(sheetNames[i]);
    var archiveSheet = archiveSS.getSheetByName(sheetNames[i]);

    var date = inputSheet.getRange('A1').getValue();
    var data = inputSheet.getRange('E7:U37').getValues().filter(function(row) { return row[0] !== '' || row[1] !== ''});

    for (var x = 0; x < data.length; x++) {
        data[x].splice(0, 0, date);
    }
    var getDate = archiveSheet.getRange(archiveSheet.getLastRow(), 1).getValue();
    var maxRowLength = data.reduce(function(length, row) { return Math.max(length, row.length); }, 0);
    var date = new Date(date); 
    var getDate = new Date(getDate);
    if (getDate.getDate() != date.getDate() || getDate.getMonth() != date.getMonth()) {     

        if (data.length != 0) {
            archiveSheet.insertRowsAfter(archiveSheet.getLastRow(), data.length);
            archiveSheet.getRange(archiveSheet.getLastRow() + 1, 1, data.length, maxRowLength).setValues(data);
        } else {
            archiveSheet.insertRowsAfter(archiveSheet.getLastRow(), 1);
            archiveSheet.getRange(archiveSheet.getLastRow() + 1, 1, 1, 2).setValues([[date, 'No Data']]);
        }
    }

  }
   }

回答1:

Assuming that the sheets that you want this in are in the same spreadsheet then all you have to do is go to A1 and enter this =nameOfSpreadsheet() Yes, I know it's customary to capitalize all of the letters but I generally swim against the currents of following customs. You may capitalize all of them if you wish.

function nameOfSpreadsheet()
{
  return SpreadsheetApp.getActive().getName();
}

All of course you will have to put the function into a project in your script editor. I keep my custom functions like this in a separate project from other functions.

I didn't pay attention to removing the week day so I find that this will work for that and now you just get the date.

function nameOfSpreadsheet()
{
  var s=SpreadsheetApp.getActive().getName().replace(/(\d{1,2}\.\d{1,2}\.\d{1,2}).*/,'$1');
  return s;
}

This is the function in the code editor:

And this is how it's deployed:

The title of the spreadsheet is 09.09.17 Saturday