时间戳

首页 > 解决方案 > App script check if a column contains a certain word write date to specific column

google-apps-script - App script check if a column contains a certain word write date to specific column

问题描述

im getting the current date-time in a column. But I need the column only to change if COLUMNTOCHECK(3) cells have text "assign".

The moment a cell in COLUMN 3 changes all the cells in column 11 change to the current time. only the cell in column 11 that is in the same row as last changed " assign " should be changed.

So what I need; if 1 cell in column 3 get the value "assign" the current date-time should appear in the same row and column 11. the moment the same call changes back to something else the date-time shouldn't be touched!

I need to get the current time when an error appeared to be set in a specific cell. The moment the error appeared is the moment a cell in column 3 get the value "assign"

var COLUMNTOCHECK = 3;
var DATETIMELOCATION = [0,11];
var SHEETNAME = 'Blad1'

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 tried other code but this one gives no result back; 

function test(test1){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet()  

  var data = sheet.getDataRange().getValue();
  var data_length = data.length;  
for(var i=0; i<data_length; i++) {
   if(data[i][0] == "assign") {
  ss.getRange(i+1,11).setValue((new Date()));

  }
 }

}

I need and a date-time result in a specific cell, like 29-5-2019 11:33:26:

标签： google-apps-script

解决方案

这应该完全符合您的预期。

注意：您将无法手动运行它，它会在编辑工作表时自动运行。

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var col = e.range.getColumn();
  var val = e.range.getValue();

  //check if sheet is 'Blad1', column edited is 'C' & value is 'assign'
  if (sh.getSheetName() === 'Blad1' && col === 3 && val === 'assign') {
    var row = e.range.getRow();
    var tz = e.source.getSpreadsheetTimeZone();
    var date = Utilities.formatDate(new Date(), tz, 'dd-MM-yyyy hh:mm:ss a');

    //set date in column 11 for same row
    sh.getRange(row, 11).setValue(date);
  }
}

此脚本的时间基于电子表格的当前时区设置，如var tz. 这些可以根据您的需要很容易地更改，如果您需要更改它以满足您的要求，这里有一些示例。

//use spreadsheet timezone setting
var tz = e.source.getSpreadsheetTimeZone();

//use script timezone setting
var tz = Session.getScriptTimeZone();

//enter your own time zone
var tz = 'GMT+0';

推荐阅读