CODEWITHSUNDEEP
google-apps-script
Paul
Paul

Reputation: 31

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

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

} }

Upvotes: 0

Views: 135

Answers (1)

Tesseract
Tesseract

Reputation: 8139

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

    }
}

Upvotes: 1

Related Questions