Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-11-21
11
Medium Priority
?
101 Views
Last Modified: 2016-11-22
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
Comment
Question by:bede123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41897245
Hi,

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

Open in new window

Regards
0
 
LVL 1

Author Comment

by:bede123
ID: 41897272
sorry, this doesnt appear to be working at all.
0
 
LVL 1

Author Comment

by:bede123
ID: 41897282
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 1

Author Comment

by:bede123
ID: 41897297
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
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41897301
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
 
LVL 1

Author Comment

by:bede123
ID: 41897312
LOL, now it seems to be in col B row 10
zac
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41897313
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
 
LVL 1

Author Comment

by:bede123
ID: 41897320
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
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 41897322
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
 
LVL 1

Author Comment

by:bede123
ID: 41897325
yey! you did it. thanks so much. that would have taken me so long to figure that out.

thanks
zac
0
 
LVL 1

Author Closing Comment

by:bede123
ID: 41897327
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
This article discusses how to implement server side field validation and display customized error messages to the client.
This Micro Tutorial demonstrates how to quickly find related content for YourTango's posts using MozBar Chrome extension.
Shows how to create a shortcut to site-search Experts Exchange using Google in the Chrome browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch the Search Engine Menu: In chrome, via you…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question