Calculate Values upon form submission

[Anna]

I've tried coding in excel but very much new and challenged with coding in gsheet. I've looked up everywhere but still can not find the correct answer. I have a file for measuring logged hours. What I would want to accomplish is that every time a response is submitted on the form, it will automatically calculate and set the values on the sheet. The response from the form is sent to Sheet A, then extracted on another sheet(let's call it Sheet B) through the formula ARRAY. Sheet B has the columns D which is the hours logged based on the response submitted on the form then a column that looks up the hours limit for the particular respondent(let's say column G). I now want column H on sheet B to calculate how much hours was in excess (H=G-D). I want this to calculate and set to values so that it no longer changes even if the limit will be changed in the future and looped every time there is a response on the form. Thank you in advance.

[Edward Ulle]

A visual example would be helpful.  I'm not sure what you mean by "ARRAY", " looks up the hours limit"?  But I'm sure it can be done.  You can start by looking at SpreadsheetApp https://developers.google.com/apps-script/reference/spreadsheet/ and drilling down.  And look at onFormSubmit() trigger https://developers.google.com/apps-script/guides/triggers/installable.

[Anna]

Here's the script I'm working with

function setUpTrigger(){

ScriptApp.newTrigger('myFunction')

.forForm('11jqQgzXiCT0XH8fAlfFS8y3_XgdY69L5QfoHAFwW0rk')

.onFormSubmit()

.create();

}

function myFunction(e){

  

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet B")

  ss.getRange("H2").setValue("=G2-D2");

  var lr = ss.getLastRow();

  var fillDownRange =ss.getRange(2,8,lr-1);

  ss.getRange("H2").copyTo(fillDownRange);

}

The challenge I have now is how to set this up so that it will paste the calculated formula as values so that even if the limit is changed, previous entries will remain unchanged. I also want this to trigger per row only upon every submission, probably offset? Thanks

[Edward Ulle]

I thought "G2" value was a lookup from a table?

[Edward Ulle]

This is simply pseudo code because I don't know all the details of your spreadsheet.

function onFormSubmit(event) {

  try {

    // event is the event object { range: {}, namedValues: {}, values: [], authMode: enum, triggerUid: uid }

    let parent = e.range.getSheet().getParent(); // parent spreadsheet

    let sheetB = parent.getSheetByName("Sheet B");

    // get some table of hours limit (total unknown to me what that is)

    let hoursLimt = parent.getSheetByName("Some sheet").getRange(1,1,10,1).getValues();

    // "respondant" is in column A of the hoursLimit table and event.values[0]

    // look up respondant in the lookup table

    let respondant = hoursLimit.find( row => row[0] === event.values[0] );

    if( respondant === undefined ) throw "Respondant ["+event.values[0]+"] not found";

    sheetB.appendRow(event.values);

    SpreadsheetApp.flush();  // insure new row has been added

    // assume respondent[1] is the limit (H) and event.values[3] (D) is the form value

    // getRange(sheetB.getLastRow(),7) is column G of the new row

    sheetB.getRange(sheetB.getLastRow(),7).setValue(respondant[1]-event.values[3]);

  }

  catch(err) {

    console.log(err);

  }

}

[Anna]

Thank you very much. I will try this script. Hope I can circle back for more questions. Appreciate all your help