Solved

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

Posted on 2016-11-21
11
71 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
  • 7
  • 4
11 Comments
 
LVL 49

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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 49

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 49

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 49

Accepted Solution

by:
Rgonzo1971 earned 500 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Learn new improvements released by Google for Google Calendar. Noted in this article are simple tips and tricks that can make your everyday use of Google Calendar better.
Google is more than just a search engine. Over the years the company has developed a wide range of online services that are readily available to all users. This article highlights how one can use Google services for simple project management.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial will introduce a Google tool, which is a great way to learn more about dimensions in metrics in Google Analytics, even if you use the interface or the API.

776 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