As of a couple of weeks now, I'm getting a "Service error: Spreadsheets" error when running a script in Google Sheets. Before that, it was working fine.
It's supposed to export a PDF while deleting some of the empty rows.
I tried adding a 1000ms delay, but that didn't work.
Any help would be greatly appreciated. You can find the code below.
var sheetName = "PDF - B";
var sheetName2 = "Offertes";
var folderID = "XXX"; // Folder id to save in a folder.
var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var sourceSheet2 = sourceSpreadsheet.getSheetByName(sheetName2);
var folder = DriveApp.getFolderById(folderID);
var invoiceName = sourceSheet.getRange("B57").getValue();
var invoiceDate = sourceSheet.getRange("H57").getValue();
var invoiceBusnr = sourceSheet.getRange("J1").getValue();
var invoiceDate_2 = Utilities.formatDate(invoiceDate, "CET", "yyyy-MM-dd")
var pdfName = "[" invoiceDate_2 "] " invoiceName " - Offerte " invoiceBusnr " Ventje";
var fact = sourceSheet.getRange("H60").getValue();
var value_f1 = sourceSheet.getRange("I53").getValue();
var value_f2 = sourceSheet.getRange("I54").getValue();
var buyerRow = sourceSheet.getRange("I61").getValue();
var buyerColumn = sourceSheet.getRange("J61").getValue();
var vColumn = sourceSheet.getRange("G61").getValue();
var f_cell = sourceSheet2.getRange(buyerRow,buyerColumn);
var f_version = sourceSheet2.getRange(buyerRow,vColumn);
if (fact=="F1") {
f_cell.setValue(value_f1);
f_version.setValue(2);
}
if (fact=="F2") {
f_cell.setValue(value_f2);
}
//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder));
//repace cell values with text (to avoid broken references)
var destSheet = destSpreadsheet.getSheets()[0]
var destSheet = destSpreadsheet.getSheetByName(sheetName)
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
destRange.setValues(sourcevalues);
var imageSheet = destSpreadsheet.getSheetByName("Bus foto's");
var imageRow = destSheet.getRange("B1").getValue();
var sourceImage_1 = imageSheet.getRange(imageRow, 2, 1, 1);
var destImage_1 = destSheet.getRange(4, 2, 1, 1);
var sourceImage_2 = imageSheet.getRange(imageRow, 3, 1, 1);
var destImage_2 = destSheet.getRange(4, 7, 1, 1);
var sourceImage_3 = imageSheet.getRange(imageRow, 4, 1, 1);
var destImage_3 = destSheet.getRange(6, 2, 1, 1);
var sourceImage_4 = imageSheet.getRange(imageRow, 5, 1, 1);
var destImage_4 = destSheet.getRange(6, 7, 1, 1);
sourceImage_1.copyTo(destImage_1,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
sourceImage_2.copyTo(destImage_2,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
sourceImage_3.copyTo(destImage_3,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
sourceImage_4.copyTo(destImage_4,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
//delete redundant sheets
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i ) {
if (i % 10 == 0) { Utilities.sleep(3000); } // Pause the function for 3000 milliseconds after 10 iterations
if (sheets[i].getSheetName() != sheetName){
destSpreadsheet.deleteSheet(sheets[i]);
}
}
//delete redundant rows
var soverig14 = destSheet.getRange("B50").getValue();
var soverig13 = destSheet.getRange("B49").getValue();
var soverig12 = destSheet.getRange("B48").getValue();
var soverig11 = destSheet.getRange("B47").getValue();
var soverig10 = destSheet.getRange("B46").getValue();
var soverig9 = destSheet.getRange("B45").getValue();
var soverig8 = destSheet.getRange("B44").getValue();
var soverig7 = destSheet.getRange("B43").getValue();
var soverig6 = destSheet.getRange("B42").getValue();
var soverig5 = destSheet.getRange("B41").getValue();
var soverig4 = destSheet.getRange("B40").getValue();
var soverig3 = destSheet.getRange("B39").getValue();
var soverig2 = destSheet.getRange("B38").getValue();
var soverig1 = destSheet.getRange("B37").getValue();
var soverig_t = destSheet.getRange("B36").getValue();
var soverig_m1 = destSheet.getRange("B35").getValue();
var sschuif = destSheet.getRange("B34").getValue();
var boverig3 = destSheet.getRange("B26").getValue();
var boverig2 = destSheet.getRange("B25").getValue();
var boverig1 = destSheet.getRange("B24").getValue();
var boverig0 = destSheet.getRange("B23").getValue();
var boverig_t = destSheet.getRange("B22").getValue();
var boverig_m1 = destSheet.getRange("B21").getValue();
var f1 = destSheet.getRange("B53").getValue();
var f2 = destSheet.getRange("B54").getValue();
var f3 = destSheet.getRange("B60").getValue();
var f4 = destSheet.getRange("B61").getValue();
if (f4=="") {
destSheet.deleteRow(61);
}
if (f3=="") {
destSheet.deleteRow(60);
}
if (f2=="") {
destSheet.deleteRow(54);
}
if (f1=="") {
destSheet.deleteRow(53);
}
if (soverig14=="") {
destSheet.deleteRow(50);
}
if (soverig13=="") {
destSheet.deleteRow(49);
}
if (soverig12=="") {
destSheet.deleteRow(48);
}
if (soverig11=="") {
destSheet.deleteRow(47);
}
if (soverig10=="") {
destSheet.deleteRow(46);
}
if (soverig9=="") {
destSheet.deleteRow(45);
}
if (soverig8=="") {
destSheet.deleteRow(44);
}
if (soverig7=="") {
destSheet.deleteRow(43);
}
if (soverig6=="") {
destSheet.deleteRow(42);
}
if (soverig5=="") {
destSheet.deleteRow(41);
}
if (soverig4=="") {
destSheet.deleteRow(40);
}
if (soverig3=="") {
destSheet.deleteRow(39);
}
if (soverig2=="") {
destSheet.deleteRow(38);
}
if (soverig1=="") {
destSheet.deleteRow(37);
}
if (soverig_t=="") {
destSheet.deleteRow(36);
}
if (soverig_m1=="") {
destSheet.deleteRow(35);
}
if (sschuif=="") {
destSheet.deleteRow(34);
}
if (boverig3=="") {
destSheet.deleteRow(26);
}
if (boverig2=="") {
destSheet.deleteRow(25);
}
if (boverig1=="") {
destSheet.deleteRow(24);
}
if (boverig0=="") {
destSheet.deleteRow(23);
}
if (boverig_t=="") {
destSheet.deleteRow(22);
}
if (boverig_m1=="") {
destSheet.deleteRow(21);
}
// export url
var url = 'https://docs.google.com/spreadsheets/d/' destSpreadsheet.getId() '/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
'&size=A4' // paper size legal / letter / A4
'&portrait=true' // orientation, false for landscape
'&fitw=true' // fit to page width, false for actual size
'&scale=4' // 1 = Normal 100% -- 2 = Fit to width -- 3 = Fit to height -- 4 = Fit to Page
// '&sheetnames=false&printtitle=false' // hide optional headers and footers
// '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
// '&fzr=false' // do not repeat row headers (frozen rows) on each page
'&horizontal_alignment=CENTER' // LEFT/CENTER/RIGHT
// '&vertical_alignment=MIDDLE' // TOP/MIDDLE/BOTTOM
// SET ALL MARGINS IN ORDER FOR IT TO WORK
'&top_margin=0.4' // set top margin
'&bottom_margin=0.4' // set bottom margin
'&left_margin=0.7' // set left margin
'&right_margin=0.7' // set right margin
'&gid=' destSheet.getSheetId(); // the sheet's Id
var token = ScriptApp.getOAuthToken();
// request export url
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' token
}
});
var theBlob = response.getBlob().setName(pdfName '.pdf');
// delete pdf if already exists
var files = folder.getFilesByName(pdfName);
while (files.hasNext())
{
files.next().setTrashed(true);
}
// create pdf
var newFile = folder.createFile(theBlob);
// return true;
//Delete the temporary sheet
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}
CodePudding user response：
I have also noticed "Service error: Spreadsheets" when trying to create a pdf; this started occurring sometime between 7 April and 28 April.
I have narrowed it down to to a column in my spreadsheet which contains images using the =IMAGE() function. If these are copied using Range.setValues() the "Service error" occurs. This in itself seems to me to be a bug as it's not very informative about what's wrong. It's arguable whether the =IMAGE() should be copyable using setValues()
However, I've discovered it is possible to copy the image formula using Range.setFormulas(), and it's also possible to use Range.setValues() on an image created using menu>insert>image but setValues() does not work if the image is created from a script using SpreadsheetApp.newCellImage()
The last mentioned problem means there is no bypass for scripts to avoid "Service error: spreadsheets" for scripts without some fiddly recoding.
Here's a shared spreadsheet which illustrates the problem: https://docs.google.com/spreadsheets/d/1xJoOf0DJBRUTbaskjKsly3pAGNJ72sT_EV8Tn246Ufo/edit?usp=sharing
"Service error" is also reported on the google issuetracker
CodePudding user response：
Based on the comment of @Tyler2P, I created a workaround, replacing the part from //repace cell values with text with the following code:
var destSheet = destSpreadsheet.getSheets()[0];
var destSheet = destSpreadsheet.getSheetByName(sheetName);
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourceRange1 = sourceSheet.getRange(1,1,3,sourceSheet.getMaxColumns());
var sourceRange2 = sourceSheet.getRange(7,1,(sourceSheet.getMaxRows()-7),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getValues();
var sourcevalues1 = sourceRange1.getValues();
var sourcevalues2 = sourceRange2.getValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
var destRange1 = destSheet.getRange(1,1,3,sourceSheet.getMaxColumns());
var destRange2 = destSheet.getRange(7,1,(sourceSheet.getMaxRows()-7),sourceSheet.getMaxColumns());
destRange1.setValues(sourcevalues1);
destRange2.setValues(sourcevalues2);