在电子表格中包含工作表标题 [英] Including the worksheet title in the spreadsheet

问题描述

我每天都有一张标题为07.09.17的工作表，里面有4张表格，我想在A1单元格中添加07.09.17（或表格的当天）？很显然，每天都有相应的日期，但在运行备份脚本时，我希望它将单元格A1作为要输入到归档表单中的日期。除了这个，我还有其他的工作。由于我们提前预订了一些预订，所以我不能在我们=现在（）或=今天（），因为我提前3个月，他们不仅在当天访问，而且还有很多天。但是，备份的一天是工作表的日期。与许多员工，我不希望信任他们做（CTRL +）;手动插入每张纸的日期。
感谢您的帮助。

 函数onOpen（）{//此函数向电子表格添加一个自定义菜单（备份到存档），以便您可以从那里运行脚本。 
 var ui = SpreadsheetApp.getUi（）; 
 ui.createMenu（'Backup to archive'）
 .addItem（'timeStamp'，'dataBackup'）
 .addToUi（）; 
 
 
 
 
 
 
 
 
 $ b $ sheet $ ，'夜间潜水']; 
 .setActiveCell（）
 .setValue（new Date（））; 
 
 
函数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 [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']]）; 
 
 
 
 
 
 
 $ div class = h2_lin>解决方案

假设您想要的表单位于同一电子表格中，那么您只需转到A1并输入 = nameOfSpreadsheet（） 是的，我知道习惯于把所有的字母大写，但我通常会在下面的风俗中游泳。



 函数nameOfSpreadsheet（）
 {
返回SpreadsheetApp。 。getActive（）的getName（）; 
} 
  

当然，您必须将函数放入脚本中的项目编辑。我将这样的自定义函数保存在一个与其他函数分开的项目中。

我没注意删除星期几，所以我发现这样可以用于现在你只需要获取日期。

  function nameOfSpreadsheet（）
 {
 var s = SpreadsheetApp。 getActive（）。的getName（）。代替（/（\d {1,2} \.\d {1,2} \.\d {1,2}）。* /， '$ 1' ）; 
 return s; 
 
 $ / code> 

这是代码编辑器中的功能：

这就是它的部署方式：

电子表格的标题是09.09.17 Saturday

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']]);
        }
    }

  }
   }

解决方案

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

这篇关于在电子表格中包含工作表标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！