CODEWITHSUNDEEP
google-apps-script
Emilie van Eps
Emilie van Eps

Reputation: 121

Google apps script read and match cell values in different sheets

I am writing a Google script for the first time to process data from a google spreadsheet. The google sheet exist of 2 sheet, which are both based on a different google form.

The goal is to automatically match people who want to tutor other people with a subject or multiple subjects. For example (see images) Johnny should be matched to Lauren and Micheal should be matched to John.

I made this code to check for Micheal and Johnny who can tutor them. What I wanted to do is check for the subject physics if Micheal wanted to be tutored for this subject and then who could do this. Then the same for Johnny. And repeat it with the other subject (English).

In this example I started with only checking the subject; physics. This is just to see if Physics is marked "Yes" or "No".

[CODE IS IN THE END]

When I look at my Log is expect to see: No Yes No Yes Yes No

But instead I see: No Yes No

sheet with people who want to bet tutored enter image description here

sheet with tutors enter image description here

function myFunction() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    /* sheet with people who want to tutor other people */
    var lr = ss.getLastRow(); // how many rows
    var kk =
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hebber");
    /* sheet with people who want to be tutored */
    var klr = kk.getLastRow(); // how many rows

    for (var i = 2; i <= klr; i++) {

        var hemail = kk.getRange(i, 2).getValue();
        var hname = kk.getRange(i, 3).getValue();
        var hphysics = kk.getRange(i, 4).getValue();
        var henglish = kk.getRange(i, 5).getValue();

        Logger.log(hphysics);

        for (var i = 2; i <= lr; i++) {

            var gemail = ss.getRange(i, 2).getValue();
            var gname = ss.getRange(i, 3).getValue();
            var gphysics = ss.getRange(i, 4).getValue();
            var genglish = ss.getRange(i, 5).getValue();

            Logger.log(gphysics);
        }
    }
}

Upvotes: 1

Views: 778

Answers (2)

Chris
Chris

Reputation: 2107

The issue seems to be your for counter variables.

See my example and it's output:

var klr = 3;
var lr = 3;

for (var i = 0; i <= klr; i++) {
  console.log("f1." + i);
  for (var i = 0; i <= lr; i++) {
    console.log("f2." + i);
  }
}

f1.0
f2.0
f2.1
f2.2
f2.3

So to answer your question you need to use an alternate variable in the 2nd for loop:

for (var i = 2; i <= klr; i++) {

    var hemail = kk.getRange(i, 2).getValue();
    var hname = kk.getRange(i, 3).getValue();
    var hphysics = kk.getRange(i, 4).getValue();
    var henglish = kk.getRange(i, 5).getValue();

    Logger.log(hphysics);

    for (var j = 2; i <= lr; j++) {

        var gemail = ss.getRange(j, 2).getValue();
        var gname = ss.getRange(j, 3).getValue();
        var gphysics = ss.getRange(j, 4).getValue();
        var genglish = ss.getRange(j, 5).getValue();

        Logger.log(gphysics);
    }
}

Upvotes: 0

Eliana Cohen
Eliana Cohen

Reputation: 369

Try for the second loop using j instead of i as a variable. Since you are using i, when the script goes back to the first for loop, it has already added numbers to the i, which causes it to leave the loop earlier than you want. You need to use different variables if you don't want the second for loop to affect the first one.

Upvotes: 1

Related Questions