Solved

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

Posted on 2016-11-21
11
77 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Working with spreadsheets can be a daunting task, especially when having to deal with large amounts of data. All you see are rows and rows of numbers and soon your eyes begin to glaze over. Take advantage of the tools in Google Sheets to create prof…
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.
This Micro Tutorial demonstrates how to create custom reports and the secrets of determine the metrics and dimensions for your data that works best with your needs.
This Micro Tutorial will demonstrate how to use comments in Google Docs. You will learn how to create, edit, and delete a comment.

840 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