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));
Advertisement
Answer
Try to add
SpreadsheetApp.flush();
Before you delete spreadsheet.