CODEWITHSUNDEEP
google-sheetsgoogle-sheets-api
Richard Robinson
Richard Robinson

Reputation: 21

Google Sheets script hangs on getDataRange or getRange

I have a google spreadsheet that allows me to keep track of costs and item amounts. I wrote a script to update these values; however, today it just hangs on getRange or getDataRange, even while debugging. This was working fine yesterday.

<code>function onOpen() {
    var menuEntries = [{name: "Update P0", functionName: "UpdateP0"}];
    SpreadsheetApp.getActiveSpreadsheet().addMenu("PI Utils", menuEntries);
}
function showMsg(data, title, timeout) {
    if (typeof title == 'undefined') title = "";
    if (typeof timeout == 'undefined') timeout = 5;
    SpreadsheetApp.getActiveSpreadsheet().toast(data, title, timeout);
}
function UpdateP0() {
    showMsg("Updating...", "", -1);
    var piSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PI (Set1)');
    piSheet.activate();
    //SpreadsheetApp.flush();
    var numPlanets = 5;
    try {
        var dRange = piSheet.getRange('A1:F55'); // will not execute pass this line
    } catch (e) {
        throw (e);
        return;
    }
    for (var p = 1, prGroup = 1; p <= numPlanets; p++, prGroup += 11) {
        var cell1, cell2, v1, v2;
        // update extraction amounts
        for (var r = 0; r < 3; r++) {
            cell1 = dRange.getCell(prGroup + 2 + r, 3);
            cell2 = dRange.getCell(prGroup + 2 + r, 4);
            v1 = cell1.getValue().toString();
            v2 = cell2.getValue().toString();
            v1 = (v1.length == 0) ? parseFloat(0.0) : parseFloat(v1);
            v2 = (v2.length == 0) ? parseFloat(0.0) : parseFloat(v2);
            v2 += v1;
            cell1.setValue(0);
            cell2.setValue(v2);
        }
        // update production costs
        for (var c = 2; c <= 5; c++) {
            cell1 = dRange.getCell(prGroup + 9, c);
            cell2 = dRange.getCell(prGroup + 8, c);
            v1 = cell1.getValue().toString();
            v2 = cell2.getValue().toString();
            v1 = (v1.length == 0) ? parseFloat(0.0) : parseFloat(v1);
            v2 = (v2.length == 0) ? parseFloat(0.0) : parseFloat(v2);
            v2 += v1;
            cell1.setValue(0);
            cell2.setValue(v2);
        }
    }
    SpreadsheetApp.flush();
    showMsg("Completed.", "", 5);
}</code>

Upvotes: 1

Views: 574

Answers (1)

Richard Robinson
Richard Robinson

Reputation: 21

What I did to fix this issue (or maybe it's a coincidence) is to create a new sheet, with an adjusted name, and then cut & paste everything from the old sheet to the new one, and then finally delete the old sheet. Running the function worked on the new sheet.

Upvotes: 1

Related Questions