Search code examples
javascriptgoogle-sheetsgoogle-sheets-query

#ERROR! occurs, but disappears when clicking on the cell and pressing return

I have a google sheet with some script attached. There's an onOpen() function that creates a menu in the UI which is a list of columns that can be sorted. The individual sort functions, called by selecting a menu item, are variations (with different columns sorted) of the below script.

The issue: on the sheet, the function returns "#ERROR!" and when clicking the function-containing-cell, and simply pressing enter, the cell magically fixes itself and displays the correct range entirely as it should.

I don't know whether there's an issue with the formula, or with the script. Current ideas as to what might be the problem are: Something to do with the fact that the formula references another sheet. Any ideas as to where to start or why the initial error (that apparently doesn't remain after recalculating the cell) occurs?

function sortJoindate() {
var sortvar = "Join Date";
var sheet = SpreadsheetApp.getActiveSheet();
var currentSort = sheet.getRange("A2").getValue();
var pmdata = sheet.getRange("A3");

if (currentSort == "SORTED BY " + sortvar + " ASC") {

sheet.getRange("A2").setValue("SORTED BY Join Date DESC");
pmdata.setValue("=QUERY(MemberData!A:J,\"select A,D,E,F,C,H,B where J = '\" & A1 & \"' order by A DESC \"");

} else {

sheet.getRange("A2").setValue("SORTED BY " + sortvar + " ASC");
pmdata.setValue("=QUERY(MemberData!A:J,\"select A,D,E,F,C,H,B where J = '\" & A1 & \"' order by A ASC \"");

}
SpreadsheetApp.flush();
}

After running script, BEFORE pressing enter on cell After running script, AFTER pressing enter on cell

Solution

  • Found the problem, google sheets in all its wisdom and helpfulness was correcting my parse error when I pressed enter, and I wasn't looking thoroughly enough at the difference between what I was feeding it and what it was correcting to. I forgot a bracket at the end.

    INCORRECT:

    pmdata.setValue("=QUERY(MemberData!A:J,\"select A,D,E,F,C,H,B where J = '\" & A1 & \"' order by A DESC \"");

    CORRECT:

    pmdata.setValue("=QUERY(MemberData!A:J,\"select A,D,E,F,C,H,B where J = '\" & A1 & \"' order by A DESC \")");

    Difference is the bracket 4 characters from the end... glad I ended up finding it. Check your brackets ppl.