How to get a Script to add the sum of 2 variables to a 3rd column in Sheets

Question

I have a 2 separate scripts that ask users for their start mileage (entered into column 3) and end mileage (entered into column 4). How can i get the total miles entered into column 10 after the end mileage is entered? I have tried entering a function on the sheet but that gets overwritten by the next data input.

My 2 script look like this:

var StartMileage =  SpreadsheetApp.getUi().prompt("Please enter Start Mileage.").getResponseText();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Retrieval Log P1CU X")

  var NewLog = ss.getRange(ss.getLastRow(), 3).setValue( StartMileage );

and,

var Home =  SpreadsheetApp.getUi().prompt("What is your end mileage?").getResponseText();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Retrieval Log P1CU X")

  var NewLog = ss.getRange(ss.getLastRow(), 4).setValue( Home );

The basic sum i want is the value of 'Home' minus the value of 'StartMileage' placed into column 10.

The script is set to run on the click of a button in which they will have to submit a response, the first button will ask their name and start mileage, the last button will ask their end mileage, the buttons will be clicked in order and both submissions will submit onto the same row. I want the script to do the calculations at the same time the end mileage is submitted, that function looks like this:

function promptHome()
{
  var Home =  SpreadsheetApp.getUi().prompt("What is your end mileage?").getResponseText();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Retrieval Log P1CU X")

  var NewLog = ss.getRange(ss.getLastRow(), 4).setValue( Home );

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Retrieval Log P1CU X")

  var NewLog = ss.getRange(ss.getLastRow(), 9).setValue( new Date() );
}

Basic premise would be: get the values of col 3 and col 4, do the calculations, insert the result into col 10. But exactly how and when to do this in your script will depend on your script, and how users interact with it. You did not provide any info about that, like: do users enter both values at the same time, or at different points in time? in predefined order or is the order up to them? can they enter only one value and never enter the other? is you script run onOpen or from add-on menu? To get the beast answer, please Add these details to your question to get the best answer. — azawaza

azawaza azawaza · Accepted Answer · 2018-06-03T11:42:12

Add the code to calculate and insert the calculated value to your promptHome() function. And there is no need to get the sheet twice, as you do in your code now. Here is what the function might look like:

function promptHome() {
  var Home = SpreadsheetApp.getUi().prompt("What is your end mileage?").getResponseText();
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Retrieval Log P1CU X");
  var startMileage = parseInt(ss.getRange(ss.getLastRow(), 3).getValue(), 10);
  var diffMileage = parseInt(Home, 10)-startMileage;
  ss.getRange(ss.getLastRow(), 4).setValue(parseInt(Home, 10));
  ss.getRange(ss.getLastRow(), 9, 1, 2).setValues([[new Date(), diffMileage]]); // insert values into cols 9 and 10 at the same time, so save time
  SpreadsheetApp.flush(); // commit the changes
}

Of course, it will work properly only if your users enter numbers only in your prompts. But if you allow them to enter whatever they want (i.e. "15mi", or "shamalamamumu") and do not validate the data, you have bigger problems than the function not working :) You should also use Lock Service in your code to prevent concurrent data writes.

How to get a Script to add the sum of 2 variables to a 3rd column in Sheets

1 Answers