Copy data from one sheet, append date to rows and paste

2
votes

I've done some reading but my limited knowledge on scripts is making things difficult. I want to:

  1. Copy a data range (C3:F20) from one sheet
  2. Paste that data in a new sheet
  3. In the new sheet, add today's date formatted (DD/MM/YYYY) in a new column

I've managed to get 1 and 2 working, but can't work out the 3rd. See current script below. 

function Copy() {
 var sss = SpreadsheetApp.openById('ID#');
 var ss = sss.getSheetByName('Workout');
 var range = ss.getRange('C3:F20');
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('ID#');
 var ts = tss.getSheetByName('Log');
 ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
}

What I have:

enter image description here

What I want:

enter image description here

3
google-apps-scriptgoogle-sheets
To create a date object, use JavaScript. var d = new Date() Date() is a function. It's not a method. After getting the date object, you can format the date with Utilities.formatDate() Link to Apps Script Doc - formatDate Please read the troubleshooting guide: Link to Apps Script troubleshooting - Alan Wells
Thank you for info and links. I'll do some further reading - S Chalk

3 Answers

0
votes

Try this.

function copy() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var range = 'a1:d8';

  var sS = s.getSheetByName('Workout');
  var sRange = sS.getRange(range);
  var sRow1 = sRange.getRow();
  var sRow2 = sRange.getLastRow();
  var sCol1 = sRange.getColumn();
  var sCol2 = sRange.getLastColumn();

  var tS = s.getSheetByName('Log');
  sRange.copyValuesToRange(tS, sCol1, sCol2, sRow1, sRow2);

  var tRange = tS.getRange(sRow1, sCol2 + 1, sRow2 - sRow1 + 1, 1);
  var d = Utilities.formatDate(new Date(), 'GMT', 'dd/mm/yyyy');
  tRange.setValue(d);
}
0
votes

When your sample table image which has data of 'a2:d8' is used, also following script can be written. This script retrieves data of 'a2:d8' from 'Workout' and copies the data to next row of last row of 'Log'. Before the data is copied, today's date is added to the data.

function Copy() {
  var ss = SpreadsheetApp.openById('ID#')
  var data = ss.getSheetByName('Workout').getRange('a2:d8').setNumberFormat('@').getValues();
  [i.push(Utilities.formatDate(new Date(), 'GMT', 'dd/MM/yyyy')) for each (i in data)];
  var s = ss.getSheetByName('Log');
  s.getRange(s.getLastRow() + 1, 1, data.length, data[0].length).setNumberFormat('@').setValues(data);
}

In this script, data is imported to an array, and the data is processed using the array. '00' of 'd2:d8' can be got without changing format using "setNumberFormat('@')".

I hope this will help for you.

0
votes

Thanks for your help @Tanaike. Here's the final code with a bit of renaming. 

function Copy() {
    var doc = SpreadsheetApp.openById('1SsE1KceJ9RqgKPBuneCSb-MaRQvSxIVJC8Bi-EUZ2os')
    var sheet1 = doc.getSheetByName('Workout');
    var range = sheet1.getRange(3, 3, sheet1.getLastRow() - 2,‌ 4);
    var data = range.setNumberFormat('@').getValues();
    [i.push(Utilities.formatDate(new Date(), 'GMT', 'dd/MM/yyyy')) for each (i in data)];
    var sheet2 = doc.getSheetByName('Log');
    sheet2.getRange(sheet2.getLastRow() + 1, 1, data.length, data[0].length).setNumberFormat('@').setValues(data);
}