CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
S Chalk
S Chalk

Reputation: 23

How can I copy data from one sheet, append date to rows and paste?

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

Upvotes: 2

Views: 978

Answers (3)

Sangbok Lee
Sangbok Lee

Reputation: 2229

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);
}

Upvotes: 0

Tanaike
Tanaike

Reputation: 201408

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('@')".

Upvotes: 0

S Chalk
S Chalk

Reputation: 23

Here's the final code with a bit of renaming, thanks to @Tanaike:

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);
}

Upvotes: 0

Related Questions