CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggerstimestamp
raphaelsword
raphaelsword

Reputation: 213

Adding a timestamp when specific data is entered from another sheet in Google Sheet

I have a Google Sheet containing two sheets, one named Form and the other Data. Information on the Data sheet is usually entered via the Form sheet, where a button click on the Form sheet sends the data over to the bottom row on the Data sheet.

I'm trying to figure out how I can add a timestamp to the appropriate cell in column E on the Data sheet when a cell on the same row in column D receives the word 'CLEANED' either as a submission from the Form sheet or as a direct manual entry on the Data sheet.

This code currently adds the timestamp, but only when I change a value in column D directly on the Data sheet (not when values are added via the Form sheet), and it adds the timestamp for any value that's added.

var ColumnToCheck = 4;
var DateTimeLocation = [0,1];
var sheetName = 'Data'

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  if( sheet.getSheetName() == sheetName ) { 
  var selectedCell = ss.getActiveCell();
  if( selectedCell.getColumn() == ColumnToCheck) { 
  var dateTimeCell = selectedCell.offset(DateTimeLocation[0],DateTimeLocation[1]);
  dateTimeCell.setValue(new Date());
      }
  }
}

I am using this code to transfer data from the Form sheet:

function submitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form");
var dataSheet = ss.getSheetByName("Data");

var values = formSS.getRange("B2:B5").getValues().reduce(function(a, b) {
    return a.concat(b)
});
var partNum = values[0];
var row;
dataSheet.getDataRange().getValues().forEach(function(r, i) {
    if (r[0] === partNum) {
        row = i + 1
    }
})
row = row ? row : dataSheet.getLastRow() + 1;
var data = dataSheet.getRange(row, 1, 1, 4).getValues()[0].map(function (el, ind){
  return el = values[ind] ? values[ind] : el;
  })
dataSheet.getRange(row, 1, 1, 4).setValues([data]);
formSS.getRange("B2:B5").clearContent()
}

What would be the best way to have the date added automatically when only the word 'CLEANED' is entered? The date shouldn't clear if the word 'CLEANED' is removed or changed to a different word.

EDIT - SOLUTION

Thanks to Sourabh Choraria, I was able to change my UPDATE function to this in order to incorporate my needs (my additions to his code are commented):

function submitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form");
var dataSheet = ss.getSheetByName("Data");

var values = formSS.getRange("B3:B6").getValues().reduce(function(a, b) {
    return a.concat(b)
});
var partNum = values[0];
var row;
dataSheet.getDataRange().getValues().forEach(function(r, i) {
    if (r[0] === partNum) {
        row = i + 1
    }
})
row = row ? row : dataSheet.getLastRow() + 1;
var data = dataSheet.getRange(row, 1, 1, 4).getValues()[0].map(function (el, ind){
  return el = values[ind] ? values[ind] : el;
  })

var statusValue = formSS.getRange("B6").getValue();   //added

if (statusValue != 'CLEANED') {                       //added
dataSheet.getRange(row, 1, 1, 4).setValues([data]);   //added

}                                                     //added

if (statusValue == 'CLEANED') {                       //added

var now = [new Date()];
var newData =  data.concat(now)
dataSheet.getRange(row, 1, 1, 5).setValues([newData]);

 }                                                    //added

formSS.getRange("B3:B6").clearContent()
}

Upvotes: 2

Views: 343

Answers (1)

Sourabh Choraria
Sourabh Choraria

Reputation: 2331

Update the submitData() function with the following code -

function submitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form");
var dataSheet = ss.getSheetByName("Data");

var values = formSS.getRange("B2:B5").getValues().reduce(function(a, b) {
    return a.concat(b)
});
var partNum = values[0];
var row;
dataSheet.getDataRange().getValues().forEach(function(r, i) {
    if (r[0] === partNum) {
        row = i + 1
    }
})
row = row ? row : dataSheet.getLastRow() + 1;
var data = dataSheet.getRange(row, 1, 1, 4).getValues()[0].map(function (el, ind){
  return el = values[ind] ? values[ind] : el;
  })

// modification begins here
var now = [new Date()];
var newData =  data.concat(now)
dataSheet.getRange(row, 1, 1, 5).setValues([newData]);
// modification ends here  

formSS.getRange("B2:B5").clearContent()
}

Basically, I'm declaring a new array variable (now) and concatenating it to the existing data array, thus forming a "newData" variable and then updating the setValues function to accommodate this change.

Hope this helps and thanks for sharing the view access to the sheet - I made a copy of that and tested the solution directly :)

Edit note1

As for updating the sheet only when the dropdown selected is 'CLEANED', kindly modify your onEdit() function to accommodate the following IF condition -

var statusValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form').getRange("B6").getValue();
if (statusValue == 'CLEANED') {
  // update timestamp as required
}

Upvotes: 2

Related Questions