Google Add Script Loop and Array

Question

I am trying to loop on every cell on my spreadsheet and look for a certain cell that is not equal to 100. I was able to loop over each cell for each column and row. As a test, I input 5 or 6 cells with different value. I am trying to use Array to get the value and show it to my Log, but every time I did that it always show me 1.0 answer.

Google App Script:

function countValue() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getValues();
  spreadsheet.forEach(function(row) {
    row.forEach(function(col){
      if (col != 100) {
        var notNumber100 = [];
        var invalidNumber = notNumber100.push(col);
        Logger.log(invalidNumber);
      };
    });
  });
}

Below is the log that I get every time I run the script. The invalid value that I put has a length of 5 or 6.

Tanaike · Accepted Answer · 2021-06-03 00:02:56Z

2

I believe your goal as follows.

You want to search the cells which has no value of 100 and put the different value to the searched cells. (As a test, I input 5 or 6 cells with different value.)

Modification points:

In your script, at var invalidNumber = notNumber100.push(col);, invalidNumber is the length of the array of notNumber100. And, the array of notNumber100 is redeclared every loop. By this, the array length is always 1. As the result, you see the value of 1 at the log. I think that this is the reason of your issue.

If you want to check the values of array and the cell coordinates, you can modify as follows.

Modified script 1:

In this modification, the active range is used. This is from your script.

function countValue() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
  var startRow = range.getRow();
  var startCol = range.getColumn();
  var spreadsheet = range.getValues();
  spreadsheet.forEach(function(row, i) {
    row.forEach(function(col, j){
      if (col != 100) {
        var notNumber100 = [];
        notNumber100.push(col);
        Logger.log("row: %s, col: %s, valueOfnotNumber100: %s", startRow + i, startCol + j, notNumber100);
        // range.offset(i, j, 1, 1).setValue(6); // When you use this line, the cell value is replaced with the value of `6`.
      };
    });
  });
}

If you want to search all cells in a sheet, you can also use the following script.

function countValue() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getDataRange();
  range.getValues().forEach(function(row, i) {
    row.forEach(function(col, j){
      if (col != 100) {
        var notNumber100 = [];
        notNumber100.push(col);
        Logger.log("row: %s, col: %s, valueOfnotNumber100: %s", i + 1, j + 1, notNumber100);
        // range.offset(i, j, 1, 1).setValue(6); // When you use this line, the cell value is replaced with the value of `6`.
      };
    });
  });
}

Modified script 2:

For example, if you want to search the cells with no value of 100 and replace it with other value, I think that you can also use TextFinder. When TextFinder is used, the sample script is as follows and the process cost can be reduced a little.

function countValue() {
  // Search the cells 
  var textFinder = SpreadsheetApp.getActiveSheet().createTextFinder("^(?!100).+$").useRegularExpression(true).matchEntireCell(true);
  
  // Confirm the cell coordinates and the cell values.
  textFinder.findAll().forEach(r => {
    Logger.log("row: %s, col: %s, cellValue: %s", r.getRow(), r.getColumn(), r.getValue());
  });
  
  // Replace the searched cells with the value of `6`.
  textFinder.replaceAllWith("6");
}

In this case, when var textFinder = SpreadsheetApp.getActiveSheet().createTextFinder("^(?!100).+$").useRegularExpression(true).matchEntireCell(true); is modified to var textFinder = SpreadsheetApp.getActiveSpreadsheet().createTextFinder("^(?!100).+$").useRegularExpression(true).matchEntireCell(true);, all sheets are used as the target sheets.

References:

answered Jun 3, 2021 at 0:02

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ian Over a year ago

Hey, I really appreciate your help. It actually works and now my automation is complete. I am just wondering if there is a way that we can also get the Cell ID of the cell which has no value of 100?

Tanaike Over a year ago

@ian Thank you for replying. I'm glad your issue was resolved. About your new question of I am just wondering if there is a way that we can also get the Cell ID of the cell which has no value of 100?, I would like to support you. But I cannot understand it. I apologize for my poor English skill. Can I ask you about the detail of your new question? By this, I would like to try to understand about your new question.

ian Over a year ago

Let say, the cell that has no value of 100 is C10, so instead of getting the count of the value that has not equal to 100, I want to get the cell ID which is C10.

Tanaike Over a year ago

@ian Thank you for replying. In that case, I think that you can use the script of

var res = SpreadsheetApp.getActiveSheet().createTextFinder("^(?!100).+$").useRegularExpression(true).matchEntireCell(true).findAll().map(r => r.getA1Notation());

. In this case, you can see the result value with log using console.log(res). If I misunderstood your new question, I apologize.

ian Over a year ago

It works. Thank you very much. I just started your tutorial in Skillshare and it gave me more knowledge in Google App Script. Thank you very much.

|

Cooper · Accepted Answer · 2021-06-02 20:49:09Z

1

function countValue() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const shts = ss.getSheets();//get all sheets
  const found = [];//found array
  shts.forEach(sh=>{
    sh.getDataRange().getValues().forEach((r,i) => {
      r.forEach((c,j)=>{
        if(c != 100) {
          found.push({sheet:sh.getName(),row:i+1,col:j+1,value:c});//saving data
        }
      });
    });
  });
  let html = found.map(obj=>{`<br>sheet: ${obj.sheet} row: ${obj.row} col: ${obj.col} value: ${obj.value}`});//creating html for dialog
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),'Looking at the entire spreadsheet');//display data in dialog
}

edited Jun 2, 2021 at 20:49

answered Jun 2, 2021 at 20:40

Cooper

64.2k7 gold badges28 silver badges65 bronze badges

Collectives™ on Stack Overflow

Google Add Script Loop and Array

2 Answers 2

Modification points:

Modified script 1:

Modified script 2:

References:

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Modification points:

Modified script 1:

Modified script 2:

References:

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related