How to auto run Google Apps Script and auto update the google sheet?

Question

I am a beginner in JavaScript and Google Apps Script.

I have written a simple piece of code to track the placement status of students in a class, I want the program to run automatically and make changes in the fields in real time.

For example, if a student enters 58% in 10th standard and press enter, the eligibility status should automatically change to "Not Eligible" and so on. is it possible to make a program that makes google sheet take the values automatically?

I am pasting the code below for reference. Please tell how can I make google sheet automatically take the values.

function placementMonitoring() {
    var app = SpreadsheetApp;

    //Get current active sheet
    var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
    var someCell = activeSheet.getRange(3, 6).getValue();
    Logger.log(someCell);

    //var newCell=activeSheet.getRange(3,3).setValue(someCell+1)
    for (var i = 3; i <= 59; i++) {
        var someCell1 = activeSheet.getRange(i, 6).getValue();

        //attempt code for DEBARRED
        if (someCell1 == "D") {
            var attempt = 0;
            activeSheet.getRange(i, 7).setValue(attempt);
        } else

            //attempt code for PARTIALY DEBARED 
            if (someCell1 == "PD") {
                attempt = 5;
                activeSheet.getRange(i, 7).setValue(attempt);
            } else

                // attempt code for unplaced students
                if (someCell1 = "UP") {
                    attempt = "NOT APPLICABLE";
                    activeSheet.getRange(i, 7).setValue(attempt);
                }
    }
    for (var i = 3; i <= 59; i++) {
        var someCell2 = activeSheet.getRange(i, 12).getValue();
        var someCell3 = activeSheet.getRange(i, 15).getValue();
        var someCell4 = activeSheet.getRange(i, 18).getValue();
        var someCell5 = activeSheet.getRange(i, 20).getValue();
        var someCell6 = activeSheet.getRange(i, 21).getValue();
        if (someCell2 == 60 && someCell3 == 60 && someCell4 == 60 && someCell5 == 60 &&
            someCell6 == 60) {
            var eligibility = " Eligible";
            activeSheet.getRange(i, 8).setValue(eligibility);
        } else {
            var eligibility = "Not Eligible";
            activeSheet.getRange(i, 8).setValue(eligibility);
        }
    }
}

here is picture of my spreadsheet

Hey @Omkar, I have a script that I regularly use I'm going to go find it for you. — user10039288
I'm going to need you to be a bit more specific with what you're needing though. — user10039288
Review Apps Script "simple triggers" in the reference documentation & guides. — tehhowch

Unknown Unknown · Accepted Answer · 2018-09-09T18:10:40

Ok so what I did to accomplish something similar was create a new function that has a bunch of if/else statements in it. Then I created a "Data Sheet" that is nothing more than a sheet to hold the data I needed. This script checks if cell B1 has a value of "Daryl I." and if it does then it copies the data from the "Data Sheet" to the new sheet. Anytime the data in cell B1 is changed then it automatically runs the script associated with that "Name N.".

  if (source.getRange('B1').getValue() == "Daryl I.")
      {
        var source_range = source.getRange("B5:N20");
        var target_range = target.getRange("B2:N17");
        target_range.copyTo(source_range);
      }

You need to have a function created that is named this specifically and define the columns and rows that you want it to actively check. MUST BE NAMED "function onEdit(e)".

SimpleTriggers

https://developers.google.com/apps-script/guides/triggers/

function onEdit(e) {
  if (
    e.source.getSheetName() == "Inked Not OA'd" &&
    e.range.columnStart == 2 &&
    e.range.columnEnd == 2 &&
    e.range.rowStart >= 1 &&
    e.range.rowEnd <= 1
  )

How to auto run Google Apps Script and auto update the google sheet?

1 Answers

SimpleTriggers