简化存档脚本 [英] Simplifying an Archiving script

问题描述

我在下面输入了一个脚本，我正尝试在具有多个页面的日常工作表上使用，我有一个存档表，其中的信息旨在在业务结束时复制。两套工作表的名称完全相同，便于备份。但是，我输入了大量的空白行，而不是其他信息。我有一个菜单用户界面，可以为工作人员添加一个按钮，以便在每天结束时进行备份，以便让生活更轻松。我也有一些附加到文件的其他脚本，所以不知道是否（很可能是字体和对齐脚本导致问题。
任何人都可以看到我做错了什么？

I have a script entered below which I am trying to use on a daily worksheet with multiple pages, I have an archive sheet where the information is meant to be copied across at end of business. Both sets of sheets are named identical to ease back up. However I get a lot of blank lines with the date entered and not the other information. I have a Menu UI to add a button for the staff to do a back up at end of day as well, again to make life easier. I do also have a few other scripts attached to the file so not sure if (most likely the "Font and Alignment" script is causing an issue. Can anyone see what I am doing wrong?

   function menu() { // 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('Backup', 'dataBackup')
.addToUi();
}
function dataBackup() {

var check = alert("Data Backup","Are you sure you want to backup today's entries?");
if(!check){ return; }
var inputSS = SpreadsheetApp.getActiveSpreadsheet();
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 = SpreadsheetApp.openById('146WU8RghfFqlCpCSX7n6kBAKOyxcpVKt14yhVfvYz-g');

    var date = inputSheet.getRange('A1').getValue(); // Gets todays date from cell A1
    var dataLen = inputSheet.getRange('E7:A37').getValues().filter(String); // Gets the number of entries made today
    var dataRange = inputSheet.getRange('E7:U37'); // Gets the range of cells A7:U37
    var data = dataRange.getValues().slice(0, dataLen.length); // Removes any rows that don't have a number in the '#' column

    for (var x = 0; x < data.length; x++) { // Adds todays date to the start of each row.
        var temp = data[x].splice(0, 0, date)
    }
    var getDate = archiveSheet.getRange(archiveSheet.getLastRow(), 1).getValue();        

    if (getDate != date) { // Checks for duplicate backup          

        if (data.length != 0) { // If there are entries with todays date
            archiveSheet.insertRowsAfter(archiveSheet.getLastRow(), data.length); // Inserts the required amount of rows
            archiveSheet.getRange(archiveSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data).setNumberFormat("@"); // inserts the data to the archive sheet
        } else {
            archiveSheet.insertRowsAfter(archiveSheet.getLastRow(), 1); // If there was no data, inserts 1 row
            archiveSheet.getRange(archiveSheet.getLastRow() + 1, 1, 1, 2).setValues([[date, 'No Data']]); // Inserts todays date and 'No Data'
        }
    }

}


}
    function alert(title, message)
      var ui = SpreadsheetApp.getUi();
     var alert = ui.alert(title, message, ui.ButtonSet.YES_NO);
     var response;
  if(alert == "YES"){
  response = true
  } else {
  response = false
}
  return response;
}

以下链接适用于归档表： -
https://docs.google.com/spreadsheets/d/146WU8RghfFqlCpCSX7n6kBAKOyxcpVKt14yhVfvYz- g / edit？usp =共享
以下是输入表的示例： -
https://docs.google.com/spreadsheets/d/1XwPFgVP_DlsBp4Th8pR7qKvx4Bh1zUubHabgMdDD3ck/edit?usp=sharing
谢谢。

The link below is for the archive sheet:- https://docs.google.com/spreadsheets/d/146WU8RghfFqlCpCSX7n6kBAKOyxcpVKt14yhVfvYz-g/edit?usp=sharing Below is a sample of an input sheet:- https://docs.google.com/spreadsheets/d/1XwPFgVP_DlsBp4Th8pR7qKvx4Bh1zUubHabgMdDD3ck/edit?usp=sharing Thank you.

 Below is the now working backup script.  I have an odd issue though that the menu button isn't appearing until I call it manually from the script editor?  Each time I am asked to review permissions but never had that issue before, is it because of another running script?  Maybe the font alignment one?

 }

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

}

@SpiderPig好吧，它正在工作，现在我不确定下面的脚本有什么问题。我已经将工作表名称单元格调整为A2以避免无意中删除，因为A1是打开时的活动单元格，并且有在其中输入内容的实例。但现在在档案表中，我得到日期01/01/1970，尽管A2有01.01.18。没有数据是正确的。我曾尝试将列更改为日期格式，并确保输入表格是日期格式。我也必须每次授权脚本，但是如果我能帮到的话，不想这样做。我是否可以在凌晨2点的时间触发脚本运行脚本，这是否仍需要为我制作的每个副本授权。想用一个正确的日期/日期名称复制一年的每一天。

@SpiderPig OK, it was working and now I am not sure what the issue is with the script below. I have adjusted worksheet name cell to A2 to avoid inadvertent removal as A1 is the active cell when opening and have had instances where stuff gets typed there. But now in the archive sheet I get the date 01/01/1970 even though A2 has 01.01.18. The no data is correct. I have tried changing column to Date format and made sure input sheet is Date formatted. I also have to authorise the script each time but don't want to do that if I can help it. Can I run the script on a time trigger say at 2AM, would this still need authorising for each copy I make. Want to make a copy for each day of the year with the correct day / date name.

     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')
    .addItem('Backup','dataBackup')
    .addToUi();
  }


  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('A2').getValue(); // Changed to stop 
  inadvertent cell changes, also made text white so not seen.
    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']]);
        }
    }

}
}

推荐答案

我试图改进脚本。

I tried to improve the script a bit. I also changed the way dates are compared since I felt the old way may not work reliably.

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);

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

    }
}

这篇关于简化存档脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！