Change value of cell in same row with google scrip

Question

I am working on a simple tracking tool for products with google Sheets. The top row has components ranging from 1 to 13 for a product. When all the components are checked as arrived the row automatically changes the Ready status in column R to ready. The grey areas are to determine which components do not belong to certain products.

Here is the document. https://docs.google.com/spreadsheets/d/18iX6Yp8SgfPaDRJC8L6f4xU1U9Gw8eiBINNFPT9r0Ow/edit?usp=sharing

This works ok, however I would like to have a timestamp to appear once the Ready status changes.

I found a piece of code and edited it to almost suit my needs.

function onEdit(event) {
var eventRange = event.range;
if (eventRange.getColumn() == 18) { // 18 == column R
var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 19,eventRange.getNumRows(), 19); // 19 == column S
var values = columnARange.getValues();
for (var i = 0; i < values.length; i++) {
   values[i][0] = new Date();
}
columnARange.setValues(values);
}
}

The problems is since it is an OnEdit function I have to manually edit the range specified if I want the timestamp to appear. In the example I linked above the "1"s in the R-column produce the timestamp but the formula that changes the cell to "Ready" once all empty boxes are crossed out, does not.

Is there any way to edit the function to look at the the sheet and if the word "Ready" is found in the R-column, a time stamp would apear next to it in the S-column?

This forum has helped me many times already and for that I thank everyone contributing.

UPDATE

I found this piece of code here https://productforums.google.com/forum/#!topic/docs/3Iz9y9OSLMk

function emailSendingTrigger() {
var sheet = SpreadsheetApp.getActiveSheet();

if (sheet.getName() == "Sheet Name") {

var activeCell = sheet.getRange("F21");

 if (activeCell.getValue() <500) { 

 emailBALANCETE();



}
}

}

Could it be changed so that it looks at each cell in a range and triggers a timestamp if the cell value changes to "ready"?

Something like

For each cell in range
If value "Ready"
Offset.1 select.cell
set.value New Date()

Now I know that is definitely not correct but maybe gives some insight as to what I am going for.

Totti

Answer 1

The combined use of formula and scripts means it's not as straightforward as it could be. If you were using a script to perform all the changes, you could just add a timestamp when the rest of the script runs.

As it is, one solution would be to have a script that runs every few minutes to check if column 'R' has the value 'Ready' and column 'S' has no timestamp, and if that's true = Add a current timestamp.

A simple but inelegant way to do this would be:

function timestamp(){

  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow()

  for (var i = 1; i <= lastRow; i++){

    var readyRange = sheet.getRange([i], 18);
    var readyValues = readyRange.getValues();

    var timeRange = sheet.getRange([i], 19);
    var timeValues = timeRange.getValues();

    if (readyValues == 'Ready' && timeValues == ''){ // 
      timeRange.setValue(new Date());

      }
    }
}

This will run through all the rows in the spreadsheet and check if they need a timestamp. You can then set a time trigger for this to run every once in a while.

This works fine for small spreadsheets, but I've thrown it together as a solution in a few minutes, so you'll need to make it more efficient if you intend to use it with several thousand rows.