CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
minky_boodle
minky_boodle

Reputation: 311

Google Script Timestamps onChange?

I am using a script to copy values from Column C to the Next Blank Column -- however, I would like to use a separate timestamp script to add the date (MM yyyy) in Row 1 of the same column being changed. [See Sample Data]

(For example, the first time my 'copy values' function runs, it will copy values in C2:C16 to D2:D16 ... I would like to then automatically place a timestamp with format MM yyyy in cell D1 -- then, next month, it will copy C2:C16 to E2:E16 and I'd like MM yyyy placed in E1, and so on.)

The below script is working to timestamp above a cell when I edit it, but fails to timestamp when I run my other script.

function onEdit() {
      var s = SpreadsheetApp.getActiveSheet();
      if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
        var r = s.getActiveCell();
        if( r.getColumn() >= 4 && r.getColumn() <= 20) { //checks the column
        var nextCell = r.offset(-1, 0);
          nextCell.setValue(new Date());
      }
      }
    }

I have tried changing it to an onChange function, with no success.

any help is def. appreciated!

TY!

and here is the Copy Values function mentioned above: 

function readSalesNum() {
var sheetFrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var sheetTo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

// Copy from 17th row, 4th column, all rows for one column 
var valuesToCopy = sheetFrom.getRange(2, 3, sheetFrom.getLastRow(), 1).getValues();

//Paste to another sheet from first cell onwards
sheetTo.getRange(2,sheetTo.getLastColumn()+1,valuesToCopy.length,1).setValues(valuesToCopy);
}

Upvotes: 0

Views: 1103

Answers (1)

Dean Ransevycz
Dean Ransevycz

Reputation: 953

Your onEdit() function is not being called when you run readSalesNum() because the onEdit() trigger runs only on edits made by a user. From the documentation:

onEdit()

The onEdit() trigger runs automatically when a user changes the value of any cell in a spreadsheet.

A better option would be to put the timestamp into its own function that you can call at the end of readSalesNum() as well as with a trigger. Or you could just add the timestamp at the end of readSalesNum(). Also, why not use Utilities.formatDate() (docs) to create pre-formatted timestamp, as it appears that you're relying on cell formatting for your timestamp format?

e.g. (just adding a timestamp)

function readSalesNum(){
  ... your code ...
  sheetTo.getRange(1,sheetTo.getLastColumn()+1).setValue(Utilities.formatDate(new Date(), "your_timezone", "MM yyyy")); 
}

Upvotes: 1

Related Questions