CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Bastien Soret
Bastien Soret

Reputation: 49

getRange and setValues functions returning a mismatch between data and number of columns

I'm trying to populate column A1 in the swimmerSheet with the names of the users (C column) in my userSheet matching the "Nageur" status (D column).

As I'm a newbie to Google Script, I don't know how to adapt this code below. Every solution I find doing my researches are returning an error message.

For now, the code below is setting the whole row of data I get in the userSheet to the first row of my swimmerSheet. I only want to get the full name (C column of userSheet) and put it in the first column of swimmerSheet.

function transferSwimmerInApp() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var userSheet = spreadsheet.getSheetByName('Utilisateurs');
  var swimmerSheet = spreadsheet.getSheetByName('Nageurs')
  
  // Get range with data from original sheet:
  var firstRow = 1;
  var firstCol = 3;
  var numRows = userSheet.getLastRow();
  var numCols = 3;
  var data_range = userSheet.getRange(firstRow, firstCol, numRows, numCols).getValues();

  //This is the status of the swimmer
  var status = 'Nageur';
  var check = userSheet.getRange('D2:D').getValues(); //The range to if statement against

  // Iterates through all rows of data from "Utilisateurs"
  for (var i=0;i<check.length; i++) {
    var col = data_range[i]; // Current row
    
    if(check[i] == status) { 
      swimmerSheet.getRange(2, 1, 1, 1).setValues([col]) 
   }
  }
}

Does anyone know how I could adapt it so it executes perfectly ?

Upvotes: 0

Views: 104

Answers (1)

SputnikDrunk2
SputnikDrunk2

Reputation: 4038

Recommendation

You can actually use the QUERY function to be able to populate column A1 in the 'Nageurs' with the names of the users (C column) in your 'Utilisateurs' sheet matching the "Nageur" status.

=QUERY(Utilisateurs!A:D,"SELECT C WHERE D = 'Nageur'")

The query function on the 'Nageurs' sheet:

enter image description here

Here's the 'Utilisateurs' sheet:

enter image description here

Upvotes: 1

Related Questions