Repeat function until cell in the same row changes to YES-CodePudding

In my search to automate monotonous tasks I've written a Google sheet that uses importxml commands to grab data from rest APIs, primarily about film data.

Each API call can return 1000's of results, so I've indexed it to return one at a time for each row, along with some other identifying information.

This is doing a film name search, returning the first result, and then comparing the name I used to search with the name of the film that was returned in the API call. If it matches there is a column that changes to yes.

To make crawling through results easy, I implemented an index column and then linked an increment script to a button.

function plusOne() {
 
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var value = cell.getValue() * 1;
  cell.setValue(value 1);
}

That will increment the active cell (my index) by 1.

What I want to do is attach an additional script that makes this continuously increment the active cell by 1 until column C in the same row changes to "YES".

CodePudding user response：

Sounds like a simple while-loop. The key is knowing how to check the value in column C for the same row. getRow() will get you the row number of the active cell, then use getCell() to get the cell in column C of that row.

Putting it all together:

function plusOne() {
  var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var value = cell.getValue() * 1;
  var row = cell.getRow()
  var otherCell = SpreadsheetApp.getActiveSheet().getCell(row, 3)

  while (otherCell.getValue() !== 'YES') {
    cell.setValue(value 1);
  }
}