Google App Script Optimisation

Question

Ok, I'm in need of some help optimising (where possible) and error checking my code.

My code has ran error free for 20+ weeks. Now all of a sudden, the script 'hangs' while executing the .setvalues on line 190. This is the section that archives the information.

Error received is "Service Timed Out : Spreadsheets" and "Exception: Service Error: Spreadsheets".

The Scripts runs between 2-3am on Sundays, when the servers should be less congested. The Script has also never timed out when running manually. I have not been able to replicate this error, even when tripling or quadrupling the working data.

So, I'll start.

My script runs in 4 sections.

Section 1 :

Validate information - Remove filters, Unhide Rows/columns and delete blank rows.

Section 2 :

Copy the selected sheet to a new spreadsheet and email this to selected users as an attachment in Excel format.

Section 3 :

Clear the data from the original sheet to prevent the possibility of duplication.

Section 4 :

This is the part that fails, TRY and paste the copied values into the archived spreadsheet.

Previously, there was no loop to re-attempt this. If it failed I would receive an email with the excel document.

The loop doesn't seem to be helping. Other than, it pasted half the information into my archive this weekend past.

If this helps, the data that is being moved is about 8000 rows and 15 columns, so about 120,000 cells. (Not that much)

If anyone can suggest any amendments or improvements, please feel free.

Full code below.

//******************** Menu Start ************************//

   function onOpen() {
   var ui = SpreadsheetApp.getUi();
   ui.createMenu('Admin')
      .addItem('Update to Raw', 'moveData')
      .addSeparator()
      .addSubMenu(ui.createMenu('Authorise')
          .addItem('Authorise Scripts', 'Auth'))
      .addToUi();
}

//******************** Menu End ************************//

//******************** Authorisation Start ************************//

function Auth(){

  var email = Session.getActiveUser().getEmail();
var temp = new Date();
  if (temp == "Blank") {
    // These calls will never be visited
onOpen();
moveData();
clearData();
RemoveFilter();
DeleteBlankRows();
UnhideAllRowsAndColumns();
UnhideAllRowsAndColumnsRaw();
clearDataRaw();

  } else {
    Browser.msgBox("The Backup script has now been authorized for "+email+". Each user only has to do this once.");
  }
}

//******************** Authorisation End ************************//

//******************** Clear Source Sheet Start ************************//

function clearData() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

  source.deleteRows(2,source.getLastRow()-1);

}

//******************** Clear Source Sheet End ************************//

//******************** Copy Data Start ************************// 

function ArchiveData() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");
  var targetkey = Spreadsheet.getSheetByName("Archive").getRange("C1").getValue();
  var tSpreadsheet = SpreadsheetApp.openById(targetkey);
  var target = tSpreadsheet.getSheetByName("Raw");

try{

  // Information Quality Checks

RemoveFilter();
UnhideAllRowsAndColumns();
DeleteBlankRows();


  var storedata = source.getRange(2,1,source.getLastRow(),source.getLastColumn()).getValues();
  var sn = Spreadsheet.getName();
  var URL = Spreadsheet.getUrl();
  var message = URL;
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd-MM-yyyy HH:mm");

  var subject = sn + " - Script Complete :  " + date;

  var emailTo = ["Recipient1@gmail.co.uk","Recipient2@gmail.co.uk",
                "Recipient3@gmail.co.uk","Recipient4@gmail.co.uk","Recipient5@gmail.co.uk"];

  // Google Sheets Extract Sheet Hack //
  // Create a new Spreadsheet and copy the current sheet into it//

  var newSpreadsheet = SpreadsheetApp.create("Call Log Script Export");
  source.copyTo(newSpreadsheet);

  newSpreadsheet.getSheetByName('Sheet1').activate();
  newSpreadsheet.deleteActiveSheet();
 // newSpreadsheet.getSheetByName('!Copied Sheet Name!').setName("Source Export") //

  var ssID = newSpreadsheet.getId();
  var url = "https://docs.google.com/spreadsheets/d/" + ssID + "/export?format=xlsx&id=" + ssID;

  var requestData = {"method": "GET","headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};

  var result = UrlFetchApp.fetch(url , requestData); 
  var contents = result.getContent();

  MailApp.sendEmail(emailTo, subject, message, 
  {attachments:[{fileName:"Call Log Script Export.xls", content:contents, mimeType:"application//xls"}]});




//------------------------- Move Data -------------------------//

  var senddata = target.getRange(target.getLastRow()+1, 1, source.getLastRow(),source.getLastColumn() );

//------------------------- Clear Data Call -------------------------//
        // ------------- Clears Source Sheet ------------- //

clearData();

var retryLimit = 4;
var retryDelay = 1000;
var retry;
    for (retry = 0; retry <= retryLimit; retry++) {
      try {
        // do the spreadsheet operation that might fail
        senddata.setValues(storedata);

          // Delete the wasted sheet we created, so our Drive stays tidy
        DriveApp.getFileById(ssID).setTrashed(true);
        SpreadsheetApp.flush();
        break; 
      }
      catch (e) {
        Logger.log('Failed on try ' + retry + ', exception: ' + e);
        if (retry == retryLimit) {
          throw e; 
        }
        Utilities.sleep(retryDelay);
      }  
  }

//------------------------- Copy Data Mid -------------------------//

}

//------------------------- Catch and Send Error Start -------------------------//


catch(err){
var error = err.lineNumber + ' - ' + err;
var URL = Spreadsheet.getUrl();
var sn = Spreadsheet.getName();
var date = Utilities.formatDate(new Date(), "GMT+1", "dd-MM-yyyy HH:mm");

var emailadd = ["Recipient1@gmail.co.uk","Recipient2@gmail.co.uk",
                    "Recipient3@gmail.co.uk","Recipient4@gmail.co.uk","Recipient5@gmail.co.uk"];
var subject = sn + " : Archive Script Error";
var body = URL + " - - - Date - - - " + date + " - - - Error Code - - - " +     error

MailApp.sendEmail(emailadd,subject,body);

 }

//------------------------- Catch and Send Error End -------------------------//

}

//******************** Copy Data End ************************//

//******************** Unhide Start ************************// 

function UnhideAllRowsAndColumns() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

var fullSheetRange = source.getRange(1,1,source.getMaxRows(), source.getMaxColumns() )  
source.unhideColumn( fullSheetRange );
source.unhideRow( fullSheetRange ) ;  
  }  

//******************** Unhide End ************************// 

//******************** Delete Blank Start ************************// 

function DeleteBlankRows() {

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

  var rows = source.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[1] == '') {
      source.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
}

//******************** Delete Blank End ************************// 

//******************** Remove Filter Start ************************// 

function RemoveFilter(){

  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var source = Spreadsheet.getSheetByName("Data");

var row = 1 //the row with filter 
var rowBefore = row 

source.insertRowBefore(row); //inserts a line before the filter
row++;

var Line = source.getRange(row + ":" + row); //gets filter line in A1N
Line.moveTo(source.getRange(rowBefore + ":" + rowBefore)); //move to new     line in A1N
source.deleteRow(row); //deletes the filter line

}

//******************** Remove Filter End ************************//

There was a problem with Google's servers yesterday that created the same error messages. There is the possibility that it's not your code, but was a temporary server problem at Google. — Alan Wells, Feb 15 '17 at 20:02
Hi Sandy, Thanks. This has been happening for the last couple of weekends. But never used to happen before. — Khazba, Feb 15 '17 at 21:40
Can you identify a certain piece of code that's causing this? If it's not replicable manually how are we supposed to do it in all that code? — Robin Gertenbach, Feb 15 '17 at 22:21
Sorry Robin, Thats a fair point. The part that the script is hanging on or only half executing is "senddata.setValues(storedata);" - Inside the Retry Loop, Should be line 190 in an editor. — Khazba, Feb 16 '17 at 20:00

score 0 · Answer 1 · answered Feb 16 '17 at 06:46

0

I looked over the code pretty thoroughly and I don't see any obvious problems. But I'm curious about this line:

var senddata = target.getRange(target.getLastRow()+1, 1, source.getLastRow(),source.getLastColumn() );

Since I can't see your data I can't confirm that the range height and width for senddata are the same as the dimensions of storedata and if they aren't then that could cause a problem.

answered Feb 16 '17 at 06:46

Cooper

59,616
6
23
54

Hi Cooper, Thanks for replying. The width will always remain the same, 15 columns. "SendData" is just my var for the range I want to paste the information. Which is "Storedata", length is source.getlastrow/column. – Khazba Feb 16 '17 at 10:20

score 0 · Answer 2 · edited Nov 08 '20 at 01:09

0

So here's what's bugging me:

var storedata = source.getRange(2,1,source.getLastRow(),source.getLastColumn()).getValues();
var senddata = target.getRange(target.getLastRow()+1, 1, source.getLastRow(),source.getLastColumn() );

storedata is an array that has to have the same dimensions as the range senddata.

number of rows in the storedata range is source.getLastRow()-1. number of columns in the storedata range is source.getLastColumn() = 15

number of rows in senddata is source.getLastRow() - target.getLastRow() + 1 number of columns in senddate is source.getLastColumn() = 15

so:

source.getLastRow()-1 = source.getLastRow() - target.getLastRow() + 1
// add 1 to both sides
source.getLastRow() = source.getLastRow() - target.getLastRow() + 2 
subtract source.getLastRow() from both sides
target.getLastRow() = 2 //is this true

Is that always true? Or am I just totally off the mark here.

edited Nov 08 '20 at 01:09

Davide Fiocco

5,350
5
35
72

answered Feb 16 '17 at 21:16

Cooper

59,616
6
23
54

Hi Cooper, I somehow missed this comment. Ok, the "Storedata" is me Taking the information from my archive, the "Senddata" is me telling it where I want it to go. Ie, the range I want to paste it.So getRange(StartRow,StartCol, Length of data (from Store), Width of data (from Store)) – Khazba Feb 17 '17 at 19:31
My curiosity stems from the little math I did on the bottom of the scrollbox just above these comments which concludes that target.getLastRow() is always equal to 2 and that's the row for the upper left cell in the target range and it never changes. Is that correct? – Cooper Feb 20 '17 at 15:49

Fi Teach · Answer 3 · 2017-07-18T10:50:00.190

0

"Service Timed Out : Spreadsheets"

error started happening on my script today and after a few trials what I could do to get away with that was to:

Make a full copy of the sheet (that contains script) and start using that sheet.

Cheers,

edited Jul 18 '17 at 10:50

answered Jul 18 '17 at 06:15

Fi Teach

88
9

Google App Script Optimisation

3 Answers3

Linked