Script gives Error only when executed by another script

Question

Hi i was wondering what could cause that problem that if i execute both functions, one of them doenst seem to be finished. But the weird thing is, it does the job in like 9/10 cases. So it brings an error even though it was finished. Heres the Error message: "Document ************* is missing (perhaps it was deleted, or you don't have read access?)" I was thinking since i delete a file maybe i do it too early and thats causing the error, but even with a pauser of 30 seconds it didnt change a thing. Hopefully someone has an idea whats causing the error and why it works most of the times but not always (in 1/10 cases one of the spreadsheets i created from the Excel is still in the destination folder).

Thanks in advance :)

Function no. 1:

function insertFunction() {
  // ID of source folder
  var sourceFolderId = "18ZQnfIjio2MvqfkCuF8kTKL5sukN1Txn"; 
 
  // ID of destination folder
  var destinationFolderId = "1AWz_tQa0le5EHTstQvpLNBBvl9z0kGxp"; 
 
  // Get Microsoft Excel spreadsheet file
  var files = DriveApp.getFolderById(sourceFolderId).getFilesByName("Kapazitäten Linien 2020_DOMAP.xlsm").next();
    var name = files.getName().split('.')[0]; 
    var blob = files.getBlob();
 
    var newFile = {
        title : name + '_converted',
        parents: [{id: destinationFolderId}] 
      }; 
    
    var sheetFile = Drive.Files.insert(newFile, blob, { convert: true });
    var newID = sheetFile.getId();
  Utilities.sleep(30000);
  Drive.Files.trash(newID);
  Drive.Files.emptyTrash();
  //start nächste Funktion
  insert2Function();
}

Function no.2 which is executed by no.1:

  function insert2Function() {
  // ID of source folder
  var sourceFolderId = "1fOsBuKL9Q9832lue596tWFWCUBxo5f_g"; 
 
  // ID of destination folder
  var destinationFolderId = "1AWz_tQa0le5EHTstQvpLNBBvl9z0kGxp"; 
 
  // Get Microsoft Excel spreadsheet files
  var files = DriveApp.getFolderById(sourceFolderId).getFilesByName("Kapazitäten Linien 2021_DOMAP.xlsm").next();
    var name = files.getName().split('.')[0]; 
    var id = files.getId();
    var blob = files.getBlob();
 
    var newFile = {
        title : name + '_converted',
        parents: [{id: destinationFolderId}] 
      }; 
    
    var sheetFile = Drive.Files.insert(newFile, blob, { convert: true });
    var newID2 = sheetFile.getId();
  Utilities.sleep(30000);
  Drive.Files.trash(newID2);
  Drive.Files.emptyTrash();
}

The Calculations i deleted: in no.1:

//Berechnungen
  var forecast = SpreadsheetApp.openById(newID).getSheetByName("Übersicht").getRange(15,5,1,1).getValue();
  var jahresmenge = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(5,3,1,1);
  jahresmenge.setValue(forecast);
  var bereitsGenutzeKapazitaet = SpreadsheetApp.openById("1bInkjzCfV4pnThjknZ5xnG-p52oUEeSw4MAGwxmgO-U").getSheetByName("Auswertung").getRange(8,8,1,1).getValue();

and in no.2:

var berechnungAktMenge = SpreadsheetApp.openById(newID2).getSheetByName("Linie 4").getRange(28,3,1,1).setValue("=SUMIF(Query!$K$2:$K$7083;'Linie 4'!A28;Query!$G$2:$G$7083)");
  var berechnungOee = SpreadsheetApp.openById(newID2).getSheetByName("Übersicht").getRange(41,5,1,1).setValue("=E23/(((E9-E31)*480)*E3)");
  var aktMenge = SpreadsheetApp.openById(newID2).getSheetByName("Linie 4").getRange(18,48,1,1).getValue();
  var aktMengeAktualisiert = SpreadsheetApp.openById(newID2).getSheetByName("Übersicht").getRange(23,5,1,1);
  aktMengeAktualisiert.setValue(aktMenge);
  var bisherProd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(5,3,1,1);
  bisherProd.setValue(aktMenge);
  var jahresmenge = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(5,2,1,1).getValue();
  var nochZuProduzieren = jahresmenge - aktMenge;
  var anzeigeNochZuProduzieren = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(5,4,1,1);
  anzeigeNochZuProduzieren.setValue(nochZuProduzieren);
  var kapazitaeten2021 = SpreadsheetApp.openById(newID2).getSheetByName("Linie 4").getRange(4,48,1,1).getValue();
  var schichtenNochVerfuegbar = SpreadsheetApp.openById(newID2).getSheetByName("Linie 4").getRange(24,48,1,1).getValue();
  var anzeigeKapazitaeten = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(8,2,1,1);
  anzeigeKapazitaeten.setValue(kapazitaeten2021);
  var kapazitaetNochVerfuegbar = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(8,4,1,1);
  kapazitaetNochVerfuegbar.setValue(schichtenNochVerfuegbar);
  var anzeigeKapazitaetGebraucht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(8,3,1,1);
  anzeigeKapazitaetGebraucht.setValue(kapazitaeten2021-schichtenNochVerfuegbar);
  var ueberschuessig2021 = schichtenNochVerfuegbar-(nochZuProduzieren/(aktMenge/(kapazitaeten2021-schichtenNochVerfuegbar)));
  ueberschuessig2021 = ueberschuessig2021.toFixed(0);
  var anzeigeUeberschuss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(8,5,1,1);
  anzeigeUeberschuss.setValue(ueberschuessig2021);
  var jahresschnitt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jahresmenge").getRange(11,3,1,1);
  jahresschnitt.setValue(aktMenge/(kapazitaeten2021-schichtenNochVerfuegbar));

Answer 1

Try to add

SpreadsheetApp.flush();

Before you delete spreadsheet.

Reference:

• SpreadsheetApp.flush

Answer 2

It's eludes me what are these functions doing? It looks like they convert the original file, save it into destination folded and deletes this new file after 30 sec. I wonder what do you mean 'it does the job in like 9/10 cases'? This job, as far as I can see is removed almost immediately.

Did you mean to remove the original file? Not the new one? If so, you need to change the last lines this way:

files.setTrashed(); // it removes old original file in source folder

// instead of this
// var newID = sheetFile.getId(); // here you get the new file ID
// Utilities.sleep(30000);        // wait 30 sec.
// Drive.Files.trash(newID);      // and delete the new file
// Drive.Files.emptyTrash();      // and even cover your tracks!