I was tasked with resolving this script by an ex-co-worker, but can't figure out this error:
"Exception: The number of rows in the data does not match the number of rows in the range. The data has 49 but the range has 71."
function onOpen() {
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('Create Cash Proposal PDF', 'createCashProposal')
.addToUi()
}
function createCashProposal() {
/*
Author: http://haw.productions
Date Created: October 2019
Updated by Amaya Maya February 2023
*/
let sourceSpreadsheet = SpreadsheetApp.getActive();
let pdfName = sourceSpreadsheet.getName();
var sheetName1 = "B1. Cash Proposal Cover";
var sheetName2 = "B2. Who We Are";
var sheetName3 = "B3. Comparison";
var sheetName4 = "B4. What We Found";
var sheetName5 = "B5. How We Can Help";
var sheetName6 = "B6. Project Estimate";
var sheetName7 = "B7. Cash Flow Solution";
var sheetName8 = "B8. Accounting Summary";
var sheetName9 = "B9. Work Order";
var sourceSheet1 = sourceSpreadsheet.getSheetByName(sheetName1);
var sourceSheet2 = sourceSpreadsheet.getSheetByName(sheetName2);
var sourceSheet3 = sourceSpreadsheet.getSheetByName(sheetName3);
var sourceSheet4 = sourceSpreadsheet.getSheetByName(sheetName4);
var sourceSheet5 = sourceSpreadsheet.getSheetByName(sheetName5);
var sourceSheet6 = sourceSpreadsheet.getSheetByName(sheetName6);
var sourceSheet7 = sourceSpreadsheet.getSheetByName(sheetName7);
var sourceSheet8 = sourceSpreadsheet.getSheetByName(sheetName8);
var sourceSheet9 = sourceSpreadsheet.getSheetByName(sheetName9);
var folder = DriveApp.getFolderById('1lzqXrqD9qUC9P2sqgnvkjQIieRylN7Yv');
DriveApp.getRootFolder();
Logger.log("getActive: ", sourceSpreadsheet.getId());
//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
var destSheet1 = destSpreadsheet.getSheetByName(sheetName1);
var destSheet2 = destSpreadsheet.getSheetByName(sheetName2);
var destSheet3 = destSpreadsheet.getSheetByName(sheetName3);
var destSheet4 = destSpreadsheet.getSheetByName(sheetName4);
var destSheet5 = destSpreadsheet.getSheetByName(sheetName5);
var destSheet6 = destSpreadsheet.getSheetByName(sheetName6);
var destSheet7 = destSpreadsheet.getSheetByName(sheetName7);
var destSheet8 = destSpreadsheet.getSheetByName(sheetName8);
var destSheet9 = destSpreadsheet.getSheetByName(sheetName9);
//repace cell values with text (to avoid broken references)
var sourceRange1 = sourceSheet1.getRange(1,1,sourceSheet1.getMaxRows(),sourceSheet1.getMaxColumns());
var sourceRange2 = sourceSheet2.getRange(1,1,sourceSheet2.getMaxRows(),sourceSheet2.getMaxColumns());
var sourceRange3 = sourceSheet3.getRange(1,1,sourceSheet3.getMaxRows(),sourceSheet3.getMaxColumns());
var sourceRange4 = sourceSheet4.getRange(1,1,sourceSheet4.getMaxRows(),sourceSheet4.getMaxColumns());
var sourceRange5 = sourceSheet5.getRange(1,1,sourceSheet5.getMaxRows(),sourceSheet5.getMaxColumns());
var sourceRange6 = sourceSheet6.getRange(1,1,sourceSheet6.getMaxRows(),sourceSheet6.getMaxColumns());
var sourceRange7 = sourceSheet7.getRange(1,1,sourceSheet7.getMaxRows(),sourceSheet7.getMaxColumns());
var sourceRange8 = sourceSheet8.getRange(1,1,sourceSheet8.getMaxRows(),sourceSheet8.getMaxColumns());
var sourceRange9 = sourceSheet9.getRange(1,1,sourceSheet9.getMaxRows(),sourceSheet9.getMaxColumns());
var sourcevalues1 = sourceRange1.getValues();
var sourcevalues2 = sourceRange2.getValues();
var sourcevalues3 = sourceRange3.getValues();
var sourcevalues4 = sourceRange4.getValues();
var sourcevalues5 = sourceRange5.getValues();
var sourcevalues6 = sourceRange6.getValues();
var sourcevalues7 = sourceRange7.getValues();
var sourcevalues8 = sourceRange8.getValues();
var sourcevalues9 = sourceRange9.getValues();
var destRange1 = destSheet1.getRange(1,1,destSheet1.getMaxRows(),destSheet1.getMaxColumns());
var destRange2 = destSheet2.getRange(1,1,destSheet2.getMaxRows(),destSheet2.getMaxColumns());
var destRange3 = destSheet3.getRange(1,1,destSheet3.getMaxRows(),destSheet3.getMaxColumns());
var destRange4 = destSheet4.getRange(1,1,destSheet4.getMaxRows(),destSheet4.getMaxColumns());
var destRange5 = destSheet5.getRange(1,1,destSheet5.getMaxRows(),destSheet5.getMaxColumns());
var destRange6 = destSheet6.getRange(1,1,destSheet6.getMaxRows(),destSheet6.getMaxColumns());
var destRange7 = destSheet7.getRange(1,1,destSheet7.getMaxRows(),destSheet7.getMaxColumns());
var destRange8 = destSheet8.getRange(1,1,destSheet8.getMaxRows(),destSheet8.getMaxColumns());
var destRange9 = destSheet9.getRange(1,1,destSheet9.getMaxRows(),destSheet9.getMaxColumns());
destRange1.setValues(sourcevalues1);
destRange2.setValues(sourcevalues2);
destRange3.setValues(sourcevalues3);
destRange4.setValues(sourcevalues4);
destRange5.setValues(sourcevalues5);
destRange6.setValues(sourcevalues6);
destRange7.setValues(sourcevalues7);
destRange8.setValues(sourcevalues8);
destRange9.setValues(sourcevalues9);
//delete redundant sheets
var sheets = destSpreadsheet.getSheets();
for (i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetName() != sheetName1 &&
sheets[i].getSheetName() != sheetName2 &&
sheets[i].getSheetName() != sheetName3 &&
sheets[i].getSheetName() != sheetName4 &&
sheets[i].getSheetName() != sheetName5 &&
sheets[i].getSheetName() != sheetName6 &&
sheets[i].getSheetName() != sheetName7 &&
sheets[i].getSheetName() != sheetName8 &&
sheets[i].getSheetName() != sheetName9){
destSpreadsheet.deleteSheet(sheets[i]);
}
}
//save to pdf
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);
// Display a modal dialog box with custom HtmlService content.
const htmlOutput = HtmlService
.createHtmlOutput('<p style="font-family:arial;font-weight:bold">Click to open <a href="' + newFile.getUrl() + '" target="_blank">' + pdfName + '</a></p>')
.setWidth(300)
.setHeight(80)
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
//Delete the temporary sheet
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}
The content of the presentation has changed, so I updated all the page names and combed through the rest but no luck.
//DriveApp.getRootFolder(); (<- Also not sure why this was commented out)
From your provided sample Spreadsheet, I found the reason for your current issue. The reason for your current issue is due to the images in the cells. When the images are included in the values retrieved by getValues, when the values are put to the sheet using setValues, such an error Service error: Spreadsheets occurs.
In this case, as the current workaround, copyTo is used instead of getValues and setValues. When this is reflected in your script, how about the following modification?
First, please, rename your current function name from createCashProposal() to another name. And, please copy and paste the following script to the script editor of Spreadsheet and set folderId, and save the script.
function createCashProposal() {
const folderId = "###"; // Please set your folder ID.
const sheetNames = ["B1. Cash Proposal Cover", "B2. Who We Are", "B3. Comparison", "B4. What We Found", "B5. How We Can Help", "B6. Project Estimate", "B7. Cash Flow Solution", "B8. Accounting Summary", "B9. Work Order"]; // This is from your showing script.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const folder = DriveApp.getFolderById(folderId);
const dst = DriveApp.getFileById(ss.getId()).makeCopy("tmp_convert_to_pdf", folder);
const dstSS = SpreadsheetApp.open(dst);
sheetNames.forEach(s => {
const t1 = ss.getSheetByName(s).copyTo(ss);
const r1 = t1.getDataRange();
r1.copyTo(r1, { contentsOnly: true });
const t2 = t1.copyTo(dstSS);
t2.getDataRange().copyTo(dstSS.getSheetByName(s).getRange("A1"), { contentsOnly: true });
ss.deleteSheet(t1);
dstSS.deleteSheet(t2);
});
const sheets = dstSS.getSheets();
sheets.forEach(s => {
if (!sheetNames.includes(s.getSheetName())) {
dstSS.deleteSheet(s);
}
});
SpreadsheetApp.flush();
const pdfName = ss.getName();
const blob = dstSS.getBlob().setName(pdfName);
const newFile = folder.createFile(blob);
const htmlOutput = HtmlService.createHtmlOutput('<p style="font-family:arial;font-weight:bold">Click to open <a href="' + newFile.getUrl() + '" target="_blank">' + pdfName + '</a></p>').setWidth(300).setHeight(80);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful');
DriveApp.getFileById(dstSS.getId()).setTrashed(true);
}