CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Fatema El-Sayed
Fatema El-Sayed

Reputation: 11

How do I change a set range in a google sheets script so that it can be selected in the cell?

First off, I am not a coder at all, just a teacher who's handy at googling things to make life easier. In my attendance book, I bold the times a student comes in tardy (they get a 1 if present and a 0 if absent in order to calculate attendance rate).

I found an awesome script that allows me to count the number of bold items in a range. However, the range is set and I can't specify a new range within google sheets for each student as is necessary.

I tried changing it to "function countColoredCells(countRange)" but it doesn't work as I assume there is something else I have to do within the rest of the script.

I literally have little to no coding knowledge and would really appreciate any help to solve this!

    function countboldcells() {
      var book = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = book.getActiveSheet();
      var range_input = sheet.getRange("C3:S3"); 
      var range_output = sheet.getRange("N3");
      var cell_styles = range_input.getFontWeights(); 
      var count = 0;

      for(var r = 0; r < cell_styles.length; r++) {
        for(var c = 0; c < cell_styles[0].length; c++) { 
          if(cell_styles[r][c] === "bold") { 
           count = count + 1; 
            }
          }
        }
        range_output.setValue(count); 
      }

Upvotes: 1

Views: 3682

Answers (1)

Tedinoz
Tedinoz

Reputation: 7949

range_input in the existing script is hard-coded. This is unsatisfactory because it doesn't permit analysis on a student-by-student basis. To fix this, you need to loop through the data for each student, and do 'countbold' for each student.

Let's assume that "C3:S3" is the range for a single student. Let's also assume that the data for other students is contained in each subsequent row, and that there are two header rows.

To do:

  • Work out the number of rows of student data - refer variable ALast.
  • Get the data for all students in one go. Why? Because this is more efficient than getting the data one row at a time - refer range_input discussed below.
  • Loop through each row of the data (i.e. loop by student - using a "for" loop).
  • Count the bold cells and update the results for each student - using most of your existing code;

Note:
The destination range (range_output) is calculated for each row, using getRange (row,column). This could have been done by saving values to an array, and updating all the values in a single process, but I though it was better to retain the approach the OP had already taken, and not over-complicate matters. If there are a LOT of students AND the code is taking too long to run, then updating the counts by array would be more efficient.

The input range (range_input) is defined using getRange(row, column, numRows, numColumns).

  • row = 3, the first row of data
  • column = 3, Column C
  • numRows = a calculated value (ALast minus two header rows)
  • numColumns = Columns C to S inclusive = 17 (assigned to a variable).
function so54260768() {

  // Setup spreadsheet and target sheet
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getActiveSheet();

  // get the number of students in Column A
  var Avals = book.getRange("A1:A").getValues(); // assuming rows one and two are headers
  var Alast = Avals.filter(String).length;
  //Logger.log("DEBUG: The last row on A = " + Alast);// DEBUG

  // number of columns in the data range
  var NumberofColumns = 17;

  // get the data for all students
  var range_input = sheet.getRange(3, 3, Alast - 2, NumberofColumns); // the first two rows are headers
  var cell_styles = range_input.getFontWeights();

  // start loop though each row - one row per student
  for (z = 0; z < Alast - 2; z++) {

    // set the bold counter to zero
    var count = 0;

    //loop through the cells in this row; count the cells that are bold
    for (var i = 0; i < NumberofColumns; i++) {
      if (cell_styles[z][i] === "bold") {
        count = count + 1;
      }
    }
    //Logger.log("DEBUG: row="+(z+3)+", count="+count);//DEBUG
    var range_output = sheet.getRange(z + 3, 14).setValue(count); //. row, column    
  }
}

Upvotes: 1

Related Questions