• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

google sheets, auto insert date into cell when data is moved

Hi all, can anyone give me some code that will add the current date into the next cell when the user selects 'yes'

the code below moves the row of data to a new sheet when the user selects 'yes' when the row of data arrives in the new sheets i'd also like the date to be inserted into the last column. thanks
zac

 function runOnEdit2(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "dataEntry" && r.getColumn() == 8 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1) 
    
    //var time = new Date();
   // time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
   // nextCell.setValue(time);
    
   // var target = targetSheet.getRange(targetSheet.getLastColumn() + 1, 1).setValue(Date());
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);

Open in new window



P.S. the commented out bits of code are what i have been playing with. thanks
0
bede123
Asked:
bede123
  • 7
  • 4
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try
targetSheet.getRange(targetSheet.getLastColumn() + 1, row).setValue(Date());

Open in new window

Regards
0
 
bede123Author Commented:
sorry, this doesnt appear to be working at all.
0
 
bede123Author Commented:
so this is what the code looks like now:

function runOnEdit2(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "dataEntry" && r.getColumn() == 8 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    targetSheet.getRange(targetSheet.getLastColumn() + 1, row).setValue(Date());
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
bede123Author Commented:
seems to now place the date in column H but several rows down. for example the data is on row 2 but the date has been placed on row 10
0
 
Rgonzo1971Commented:
then try
function runOnEdit2(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "dataEntry" && r.getColumn() == 8 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    targetSheet.getRange(targetSheet.getLastColumn() + 1, targetSheet.getLastRow() ).setValue(Date());
    s.deleteRow(row);

Open in new window

0
 
bede123Author Commented:
LOL, now it seems to be in col B row 10
zac
0
 
Rgonzo1971Commented:
then try
function runOnEdit2(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "dataEntry" && r.getColumn() == 8 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    targetSheet.getRange(targetSheet.getLastRow(),targetSheet.getLastColumn() + 1).setValue(Date());
    s.deleteRow(row);

Open in new window

0
 
bede123Author Commented:
ok think we are getting closer. each time i go back to the dataEntry sheet and select 'Yes' it removes the selected row and puts it in the 'Completed' sheet. however it places the date in a different coloumn each time. i just tried it three times and it placed the date in J, K, L, and so on
0
 
Rgonzo1971Commented:
then try
function runOnEdit2(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "dataEntry" && r.getColumn() == 8 && r.getValue() == "Yes") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    targetSheet.getRange(targetSheet.getLastRow(),numColumns  + 1).setValue(Date());
    s.deleteRow(row);

Open in new window

0
 
bede123Author Commented:
yey! you did it. thanks so much. that would have taken me so long to figure that out.

thanks
zac
0
 
bede123Author Commented:
works just as i had asked for. add the date/time into the last column.
all i need to do now is tweek the date format and time zone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now