my google apps script doesn't run, no parameters with serverhandler

Question

I try to change a sheets with a script but it doesn't work as expected. I can load the right panel, but nothing happens when I try to record the change. It seems "masterFunctionPS" isn't called. The function periodSelection post the panel, the listbox and the button. But nothing append, when I clic on the Button. Nothing change in the sheets.

function periodSelection() { 

  var activeSS = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheetPS = activeSS.getSheetByName("Periods"); 

  var uiPS = UiApp.createApplication().setWidth(300); 

  var panelPS = uiPS.createVerticalPanel(); 

  var periodPS = uiPS.createListBox(); 
  for (var i = 2; i < 13; i++) { 
    var range = "A" + i; 
    periodPS.addItem(sheetPS.getRange(range).getValue()); 
  } 

  var endDatePS = uiPS.createDatePicker(); 

  var recordPS = uiPS.createButton("Enregistrer"); 

  var masterPS = uiPS.createServerHandler('masterFunctionPS'); 
  masterPS.addCallbackElement(periodPS) 
          .addCallbackElement(endDatePS) 
  recordPS.addClickHandler(masterPS); 

  panelPS.add(periodPS); 
  panelPS.add(endDatePS); 
  panelPS.add(recordPS); 

  uiPS.add(panelPS); 

  SpreadsheetApp.getUi().showSidebar(uiPS); 

  return uiPS; 
} 

function masterFunctionPS(element) { 

  var parameterPS = element.parameter; 
  var appE = UiApp.getActiveApplication(); 

  var periodE = parameterPS.periodPS; 
  var endDateE = parameterPS.endDatePS; 
  var activeE = parameterPS.activeSS; 
  var sheetE = parameterPS.sheetPS; 

  switch (periodE)  { 
        case "P1": 
            sheetE.getRange("C2").setValue(endDateE); 
            break; 
        case "P2": 
            sheetE.getRange("C3").setValue(endDateE); 
            break; 
        case "P3": 
            sheetE.getRange("C4").setValue(endDateE); 
            break; 
        case "P4": 
            sheetE.getRange("C5").setValue(endDateE); 
            break; 
        case "P5": 
            sheetE.getRange("C6").setValue(endDateE); 
            break; 
        case "P6": 
            sheetE.getRange("C7").setValue(endDateE); 
            break; 
        case "P7": 
            sheetE.getRange("C8").setValue(endDateE); 
            break; 
        case "P8": 
            sheetE.getRange("C9").setValue(endDateE); 
            break; 
        case "P9": 
            sheetE.getRange("C10").setValue(endDateE); 
            break; 
        case "10": 
            sheetE.getRange("C11").setValue(endDateE); 
            break; 
        case "P11": 
            sheetE.getRange("C12").setValue(endDateE); 
            break; 
    } 
        return (appE); 
  }

Serge insas · Accepted Answer · 2014-09-26 15:20:49Z

There are a few errors in your code...mainly you forgot to give names to your widgets and name is used to retrieve values from the callbackelements below is a "rectified" version that works but there are still 2 items that won't work because you tried to get properties of the sheet in the Ui and that can't be done like this...

please explain what for you need that and I could suggest a better way to go.

(see comments in code and look at the logger to see the values in event parameters)

function periodSelection() { 

  var activeSS = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheetPS = activeSS.getActiveSheet(); 

  var uiPS = UiApp.createApplication(); 

  var panelPS = uiPS.createVerticalPanel(); 

  var periodPS = uiPS.createListBox().setName('periodPS'); 
  for (var i = 2; i < 13; i++) { 
    var range = "A" + i; 
    periodPS.addItem(sheetPS.getRange(range).getValue()); 
  } 

  var endDatePS = uiPS.createDatePicker().setName('endDatePS'); 

  var recordPS = uiPS.createButton("Enregistrer"); 

  var masterPS = uiPS.createServerHandler('masterFunctionPS'); 
  masterPS.addCallbackElement(panelPS);
  recordPS.addClickHandler(masterPS); 

  panelPS.add(periodPS); 
  panelPS.add(endDatePS); 
  panelPS.add(recordPS); 

  uiPS.add(panelPS); 

  SpreadsheetApp.getUi().showSidebar(uiPS); 
} 

function masterFunctionPS(element) { 
  var parameterPS = element.parameter; 
  var appE = UiApp.getActiveApplication(); 
  Logger.log('parameter : '+JSON.stringify(parameterPS));

  var periodE = parameterPS.periodPS; 
  var endDateE = parameterPS.endDatePS; 
  var activeE = parameterPS.activeSS; // won't work
  var sheetE = parameterPS.sheetPS; // won't work

  switch (periodE)  { 
        case "P1": 
            sheetE.getRange("C2").setValue(endDateE); 
            break; 
        case "P2": 
            sheetE.getRange("C3").setValue(endDateE); 
            break; 
        case "P3": 
            sheetE.getRange("C4").setValue(endDateE); 
            break; 
        case "P4": 
            sheetE.getRange("C5").setValue(endDateE); 
            break; 
        case "P5": 
            sheetE.getRange("C6").setValue(endDateE); 
            break; 
        case "P6": 
            sheetE.getRange("C7").setValue(endDateE); 
            break; 
        case "P7": 
            sheetE.getRange("C8").setValue(endDateE); 
            break; 
        case "P8": 
            sheetE.getRange("C9").setValue(endDateE); 
            break; 
        case "P9": 
            sheetE.getRange("C10").setValue(endDateE); 
            break; 
        case "10": 
            sheetE.getRange("C11").setValue(endDateE); 
            break; 
        case "P11": 
            sheetE.getRange("C12").setValue(endDateE); 
            break; 
    } 
        return appE; 
  }

EDIT :

I think I guess what you are trying to do...

Actually the active SS and sheet are always the same, you can just reuse it the same way ...(personally I would keep the same names all along...) see updated handler function below :

function masterFunctionPS(element) { 
  var parameterPS = element.parameter; 
  var appE = UiApp.getActiveApplication(); 
  Logger.log('parameter : '+JSON.stringify(parameterPS));

  var periodE = parameterPS.periodPS; 
  var endDateE = parameterPS.endDatePS; 
//  var activeE = parameterPS.activeSS; // won't work
//  var sheetE = parameterPS.sheetPS; // won't work
  var activeE = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheetE = activeE.getActiveSheet(); 

  switch (periodE)  { 
        case "P1": 
            sheetE.getRange("C2").setValue(endDateE); 
            break; 
        case "P2": 
            sheetE.getRange("C3").setValue(endDateE); 
            break; 
        case "P3": 
            sheetE.getRange("C4").setValue(endDateE); 
            break; 
        case "P4": 
            sheetE.getRange("C5").setValue(endDateE); 
            break; 
        case "P5": 
            sheetE.getRange("C6").setValue(endDateE); 
            break; 
        case "P6": 
            sheetE.getRange("C7").setValue(endDateE); 
            break; 
        case "P7": 
            sheetE.getRange("C8").setValue(endDateE); 
            break; 
        case "P8": 
            sheetE.getRange("C9").setValue(endDateE); 
            break; 
        case "P9": 
            sheetE.getRange("C10").setValue(endDateE); 
            break; 
        case "10": 
            sheetE.getRange("C11").setValue(endDateE); 
            break; 
        case "P11": 
            sheetE.getRange("C12").setValue(endDateE); 


             break; 
        } 
// the following line is only for test... remove it when it works!!! 
            sheetE.getRange("A1").setValue('failed to write value '+endDateE); 
         return appE; 
    }

Collectives™ on Stack Overflow

my google apps script doesn't run, no parameters with serverhandler

1 Answer 1

EDIT :

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT :

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related