Below is my script that had been working up until Friday. My research led me to believe that issue was a full destID which I updated with a destination that had just the one tab (something we've done before). Now I get this error :sadpanda: and have been beating my head against it ALL WEEKEND.
Grateful for any and all help on this!
/*********************************************Constants**************************************************************/
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
//Active sheet
var select = ss.getSelection();
var sheet = ss.getSheetByName('Form Responses 1');
var data = sheet.getDataRange().getValues();
var headers = data[0];
//Most recent row
var lastRowInd = ss.getLastRow() -1;
var lastColumnInd = ss.getLastColumn() -1;
var lastRow = data[lastRowInd];
var zenDesk = lastRow[1].toString();
//Current row
var currentRow = select.getActiveRange().getValues()[0];
var currentInd = select.getActiveRange().getRowIndex() - 1;
//add a menu item so the last response can be sent via a simple menu option
function onOpen(){
ui.createMenu('Scripts')
.addItem('Send Last as Email', 'sendEmail')
.addItem('Save Last As CSV', 'saveAsCSV')
.addItem('Send Current as Email','sendCurrentEmail')
.addToUi();
}
/* Send an email containing the form responses to CAST members */
function sendEmail(){
var response = ui.prompt('Please enter target e-mail', ui.ButtonSet.OK_CANCEL);
if (response.getSelectedButton() == ui.Button.OK) {
Logger.log('The user\'s e-mail is %s.', response.getResponseText());
} else {
Logger.log('The user clicked the close button in the dialog\'s title bar.');
}
//Update the prefilled row first.
var urls = betterBuildUrls(false)
//Build a nicely formatted string
var message_str = 'Please see the changed or updated configuration below: \n\n';
var len = lastRow[0].length
for (var col = 0; col < headers.length; col++){
var value = lastRow[col].toString()
var header_value = headers[col].toString()
Logger.log('Row: ' + value + '\n');
if(value){
message_str += header_value + ': ' + lastRow[col].toString() + '\n';
}
}
//TODO: Figure out how to export the message into a PDF.
//var pdfContent = message_str;
//var attach = {fileName:'Autogenerated template.pdf',content:planAttach, mimeType:'application/pdf'};
message_str += 'Prefilled URL: \n' + urls[0];
//message_str += '\n' + 'Editable URL: \n' + urls[1] + '\n';
//Get the e-mail from user response if exists
if(response.getResponseText().length > 0){
var emailAddress = response.getResponseText()
}else{
var emailAddress = 'cast.members@phtech.com'
}
var message = message_str
var subject = 'Peer review ready for ZD#' + zenDesk
sheet.getRange(lastRowInd, lastColumnInd).setBackground('orange');
MailApp.sendEmail(emailAddress, subject, message)
Logger.log(message)
}
function sendCurrentEmail(){
var response = ui.prompt('Please enter target e-mail', ui.ButtonSet.OK_CANCEL);
if (response.getSelectedButton() == ui.Button.OK) {
Logger.log('The user\'s e-mail is %s.', response.getResponseText());
} else {
Logger.log('The user clicked the close button in the dialog\'s title bar.');
}
//Update the prefilled row first.
var urls = betterBuildUrls(true)
//Build a nicely formatted string
var message_str = 'Please see the changed or updated configuration below: \n\n';
var len = currentRow[0].length
for (var col = 0; col < headers.length; col++){
var value = currentRow[col].toString()
var header_value = headers[col].toString()
Logger.log(header_value + ': \n' + value + '\n');
if(value){
message_str += header_value + ': ' + currentRow[col].toString() + '\n';
}
}
message_str += 'Prefilled URL: \n' + urls[0] + '\n';
message_str += '\n' + 'Editable URL: \n' + urls[1] + '\n';
//Send it all to the listed e-mail
if(response.getResponseText().length > 0){
var emailAddress = response.getResponseText()
}else{
var emailAddress = 'cast.members@phtech.com'
}
var message = message_str
var subject = 'Peer review ready for ZD#' + zenDesk
MailApp.sendEmail(emailAddress, subject, message)
select.getActiveRange().setBackground('orange')
Logger.log(message)
}
/*
function saveAsCSV() {
// create a folder from the name of the spreadsheet
var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
// append ".csv" extension to the sheet name
fileName = sheet.getName() + ".csv";
// convert all available sheet data to csv format
var csvFile = convertRangeToCsvFile();
// create a file in the Docs List with the given name and the csv data
var file = folder.createFile(fileName, csvFile);
//File downlaod
var downloadURL = file.getDownloadUrl().slice(0, -8);
showUrl(downloadURL);
}
*/
function showUrl(url) {
var html = '<html><body><a href="'+url+'" target="blank" onclick="google.script.host.close()">'+'Form Response'+'</a></body></html>';
var ui = HtmlService.createHtmlOutput(html)
SpreadsheetApp.getUi().showModelessDialog(ui,"Download");
}
/* Convert the last row of the active spreadsheet into a csv file
function convertRangeToCsvFile() {
try {
//Get data
//Concatenate headers and last row into an array so the CSV loop can correctly build string.
var data = [headers,lastRow]
var csvFile = undefined;
// loop through the data in the range and build a string with the csv data
if (data.length > 1) {
var csv = "";
for (var row = 0; row < data.length; row++) {
for (var col = 0; col < data[row].length; col++) {
if (data[row][col].toString().indexOf(",") != -1) {
data[row][col] = "\"" + data[row][col] + "\"";
}
}
// join each row's columns
// add a carriage return to end of each row, except for the last one
if (row < data.length-1) {
csv += data[row].join(",") + "\r\n";
}
else {
csv += data[row];
}
}
csvFile = csv;
}
return csvFile;
}
catch(err) {
Logger.log(err);
Browser.msgBox(err);
}
}
*/
function testIt(){
betterBuildUrls(true);
}
function betterBuildUrls(select){
//Get the the spreadsheet data we need to pre-fill our form
var date = new Date()
var timeZone = Session.getScriptTimeZone();
var date = Utilities.formatDate(date, timeZone, "MM-dd-yyyy HH:mm")
var originalID = '1s8VLhOOVrjUJJ4EEnXMYxCJvfGc35McF4OvVpqVnVgE'
var intakeForm = FormApp.openById(originalID);
var intakeUrl = intakeForm.getResponses()[0].getEditResponseUrl();
var destID = '10nHM2MSgAVaDE2q5h1f2gy8oJ2j9NyCbfftwk3QDlqc' // Data for pre-fill
var urlCol = headers.indexOf("Prefilled URL"); //Need this col so we can return the pre-filled URL link to the spreadsheet
var pubCol = headers.indexOf("Edit Url");
var indRow = '';
if(select){
currentRow =currentRow;
indRow = currentInd;
}else{
currentRow = lastRow;
indRow = lastRowInd;
};
// Create a new form, then iteratively add questions and responses
var form = FormApp.create('Peer Review Checklist').setTitle('Peer review checklist ' + date);
var formResponse = form.createResponse();
var itemNum = 1;
//Get the item values by iterating through the header and capturing the value at the currentRow
for(var i = 1; i < headers.length;i++){
if(currentRow[i].length != 0){
var item = headers[i];
var formItem = form.addParagraphTextItem()
.setTitle(item)
var response = formItem.createResponse(currentRow[i]);
formResponse.withItemResponse(response);
if(i > 2){
var item = 'Is this correct?';
var formItem = form.addCheckboxItem()
.setTitle(item)
.setChoiceValues(['Yes','No'])
.showOtherOption(true);
itemNum ++;
}
}
}
var item = "Were objects notated?"
var formItem = form.addCheckboxItem()
.setTitle(item)
.setChoiceValues(['Yes','No'])
.showOtherOption(true)
var item = "Has documentation from plan been saved in correct location?";
var formItem = form.addCheckboxItem()
.setTitle(item)
.setChoiceValues(['Yes','No'])
.showOtherOption(true);
var item = "Have supporting documents been attached to ticket?";
var formItem = form.addCheckboxItem()
.setTitle(item)
.setChoiceValues(['Yes','No'])
.showOtherOption(true);
var item = "Reviewer comments.";
var formItem = form.addParagraphTextItem()
.setTitle(item);
var item = "Reviewer Email";
var formItem = form.addTextItem().setRequired(true).setTitle(item);
/* Uncomment this to link to main sheet*/
form.setDestination(FormApp.DestinationType.SPREADSHEET, destID)
var filledUrl = form.shortenFormUrl(formResponse.toPrefilledUrl());
if(urlCol){
var urlRange = sheet.getRange(indRow+1, urlCol+1);
urlRange.setValue(filledUrl)};
if(intakeUrl){
var urlRange = sheet.getRange(indRow+1, pubCol+1);
urlRange.setValue(intakeUrl)};
return [filledUrl,intakeUrl]
Logger.log(
'Prefilled URL: ' + formResponse.toPrefilledUrl());
Logger.log('Editable URL: ' + intakeFormResponse.getEditResponseUrl());
}
I've updated the destID and I've reviewed some other Answers that might help, but my attempts to use them didn't get my script functional.
Given the information shared, it is hard to pinpoint the precise issue, as there are numerous lines to evaluate and a lack of specific spreadsheet details. Nevertheless, the error message "The starting column of the range is too small" suggests that the problem likely resides in the variables passed to the getRange() methods. Fortunately, there are only a few instances of this method in the code.
To identify the root cause, I recommend you check the following sections of the code:
var lastColumnInd = ss.getLastColumn() -1;Consider whether the '-1' is necessary for this line. The lastColumnInd variable is only used in a getRange().setBackground() call and the inclusion of '-1' may prevent the background color of the sheet's last column from being changed to 'orange'. not sure if this is intended.
var urlCol = headers.indexOf("Prefilled URL"); var pubCol = headers.indexOf("Edit Url");Verify that your spreadsheet contains these two values as headers. If these values are not present in the first row of your sheet, the variables will be assigned a value of -1, which could lead to the "The range is too small" error.