13

I'm looking for a way to add simple calculated field in a google form that will be dependent on other field's values.

The first field is a simple price field quantity * price = total_price where quantity is a numeric field entered by the user.

The second field is an end date now() + X months where X is selected by the user.

I'v taken a look at google's tutorials and found only addons which in my understanding are form wide where i'm looking for something more like a custom form field.

The best solution would be one that would calculate the fields as the user change the other input's value but a simple text in the confirmation page is also OK.

I would appreciate if anyone could point me in the right direction. Thanks.

haki
  • 9,389
  • 15
  • 62
  • 110
  • As per my knowledge, in Google forms there is no way to get the live form values before submitting the form. – KRR Apr 13 '15 at 21:27
  • What about adding a confirmation page with values calculated based on the input fields ? – haki Apr 14 '15 at 06:20

2 Answers2

6

Scripting only can affect the creation and post-submit processing of a form. You cannot make any changes to the "live" form the user is currently looking at. What you can do is send an email to the user after the form has been submitted with a calculated summery. You could include a link to the getEditResponseUrl() if they see any errors.

Spencer Easton
  • 5,642
  • 1
  • 16
  • 25
  • I relize now that live updates would be a stretch but how about adding a confirmation page with values calculated based on the input fields ? – haki Apr 14 '15 at 06:19
  • 1
    That wont work either. You can change the confirmation field onSubmit but the change won't be seen until the next time a user launches the live form. – Spencer Easton Apr 14 '15 at 12:09
  • So to make sure that we are on the same page. You claim that if i want to inject a piece of text to the confirmation page that is derived from values the user inputed in the live form this is impossible ? – haki Apr 15 '15 at 08:28
  • That is correct. If you were to calculate the values then change the setConfirmationMessage() with the values or pointing to a document with the calculated values it would not show up until the next time the live form in launched. – Spencer Easton Apr 15 '15 at 11:47
  • If you are already comfortable with Apps Script making a simple Web App is pretty straight forward. You then get all the flexibility you need. – Spencer Easton Apr 15 '15 at 12:18
1

I had this problem too. I needed to build a control board using the answers incoming from a google form, The problem: when you use formulas in a column next to the sheet where the answers are submited, google inserts a new row but does not replicate the formula, so you end up copying and pasting the formula over and over again.

To solve this i used an script that copies the cell where the formula is and pastes it in all rows where the answers were.

The script is pretty simple, first you have to open the script editor in the "tools" menu, and paste the code below. You only have to "call" the sheets with a name in the code and then mark the columns where formulas are.In this case hoja1 is "BD ingresos" and hoja2 is "DB gastos"

In this case, the sheets where i have forms deploying answers are "BD ingreso" and "BD gastos", the columns where i calculate something are A, B, C, D and E in "BD ingresos" same as in "BD Gastos".

(names in spanish, sorry)

// Activar la hoja actual 
var ss = SpreadsheetApp.getActiveSpreadsheet();
// se guarda hoja1 
var hoja1 = ss.getSheetByName("BD ingreso");
var hoja2 = ss.getSheetByName("BD gastos")

function actualizar() {

// obtener ultima fila de la hoja 
var ultimaFila = hoja1.getLastRow();

rellenarColumna("A",ultimaFila,hoja1);
rellenarColumna("B",ultimaFila,hoja1);
rellenarColumna("C",ultimaFila,hoja1);
rellenarColumna("D",ultimaFila,hoja1);
rellenarColumna("E",ultimaFila,hoja1);

// obtener ultima fila de la hoja 
var ultimaFila = hoja2.getLastRow();

rellenarColumna("A",ultimaFila,hoja2);
rellenarColumna("B",ultimaFila,hoja2);
rellenarColumna("C",ultimaFila,hoja2);
rellenarColumna("D",ultimaFila,hoja2);
rellenarColumna("E",ultimaFila,hoja2);

}


function rellenarColumna(pivote, ultimaFila,hojaTrabajo)
{
 //------------------------------------------------------------- 
 //celda que contiene la información que se va a pegar. 
  var celdaCopiarFormula = hojaTrabajo.getRange(pivote+"2");
  //obtener valor de dicha celda
 var valorCeldaCopiarFormula = celdaCopiarFormula.getFormula();

 //modificar rango para que pegue la información desde la ubicacion enviada hasta la ultima fila 
var rangoTotal = pivote + "2:" + pivote + ultimaFila; 


//definir ubicacion en donde se va a pegar la informacion
var destinoPegado = hojaTrabajo.getRange(rangoTotal);
destinoPegado.setFormula(valorCeldaCopiarFormula);   
}

Then you have to create a trigger in the script editor as this image shows. Set the trigger to launch the code "actualizar" with the event "from the spreadsheet" "on form submit"

David Alv
  • 11
  • 1