Cell reference out of range, google sheets

Question

I'm getting this error while running this sheet.

Cell reference out of range (line 81, file "genreportSE")

I don't know why it says it's 'out of range'.

I tried to used 'copyvalues'. I saw a script where you can't really "print" a range, but you can create another spreadsheet, copy that range, then print that sheet and delete it.

How should I accomplish this?

function genreportSE() {                                                                          // This function let us read the value of a cell from a sheet and change the value of another cell in a different sheet
    var ss = SpreadsheetApp.getActive();                                                            //ss stands for spreadsheet, this is the active spreadsheet
    var clientsheet = ss.getSheetByName('Clientes SE');
    var gensheet = ss.getSheetByName('Generador SE');
    var clienttable = clientsheet.getDataRange();
    var numberofservices = clienttable.getNumRows();                                                //The number of services in the Clientes sheet
    var error1;
    var error2;
    var rangetocheck1;
    var rangetocheck2;
    var client;
    var clientname;
    var i=0;                           
    var reportswitherrors = [];                                                                     //Array for faulty reports
    var email ='[email protected]';
    var subject = "Reporte de producción y consumo - " + (new Date()).toString();
    var body = "TEXT" ;

    for (i=0;i<=2;i++){
        gensheet.getRange('B2').setValue(clientsheet.getRange(i+2,1).getValue());                     //This will change the cell "B2" in "Generador SE" to the current service number for the report generation

        Utilities.sleep(3000);                                                                        //A timer to let the importdata function get the data from the SE server in miliseconds

        client = gensheet.getRange('B4').getValue;
        clientname = String(client);
        rangetocheck1 = gensheet.getRange('B8:C14').getValues();                                      //Data range that could present calculation errors   ********
        rangetocheck2 = gensheet.getRange('H8:H14').getValues();                                      //Data range that could present calculation errors   ********

        if(String(rangetocheck1).indexOf('#N/A') == -1) {                                             //This checks if there are any errors in rangetocheck1
            error1 = false;
        } else {
            error1 = true;
        };

        if(String(rangetocheck2).indexOf('#N/A') == -1) {                                             //This checks if there are any errors in rangetocheck2
            error2 = false;
        } else{
            error2 = true;
        };

        if(error1||error2){
            reportswitherrors.push(clientsheet.getRange(i+2,1).getValue());                               //This appends the current service number to the faulty services array
        } else {
            // Convert individual worksheets to PDF
            var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export",15,60);
            newSpreadsheet.getSheetByName('Sheet1').activate();
            var newsheet = newSpreadsheet.getSheetByName('Sheet1');
            var genRange = gensheet.getRange('A1:H50').copyValuesToRange(newsheet,0,10,0,55)

            var pdf = DriveApp.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
            var attach = {fileName:'Weekly Status.pdf',content:pdf, mimeType:'application/pdf'};
            MailApp.sendEmail(email, subject, body, {attachments:[attach]});

            DriveApp.getFileById(newSpreadsheet.getId()).setTrashed(true);
        }
    };

    Logger.log(reportswitherrors);
}

You've posted about 60 lines of code (which is more than should be necessary), but you have an error on line 81. Which line is that, in this snippet? Try reading the question as someone who might answer it; is there enough info? Is the info consistent? — Mogsdad

Mogsdad Mogsdad · Accepted Answer · 2016-01-23T04:30:26

It appears that you've got your row & column dimensions flipped between function calls. (Because Google decided to be inconsistent with the order of them...)

This line calls create(name, rows, columns):

var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export",15,60);

You've created a spreadsheet with 15 rows and 60 columns.

A bit further along, probably on line 81, copyValuesToRange(sheet, column, columnEnd, row, rowEnd) gets invoked:

var genRange = gensheet.getRange('A1:H50').copyValuesToRange(newsheet,0,10,0,55)

Cell reference out of range, google sheets

1 Answers