Solved

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

Posted on 2016-11-21
11
81 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 50

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
Technology Partners: 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!

 
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 50

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 50

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 50

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ModalPopup  question 22 38
Can't get js code to execute 8 30
Power Query Grouping By 2 15
Table style position to the left 8 24
Learn about cloud computing and its benefits for small business owners.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 demonstrate without sampling how to find out top organic landing pages. The hack gets around the standard way to find the pages in Google Analytics results in sampling for larger sites.

762 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