Copy entire row from one Google Sheet to another, based on date in column A

1,633 views
Skip to first unread message

Ian Taylor

unread,
Jan 21, 2021, 4:35:49 AM1/21/21
to Google Apps Script Community

Hello, please be gentle with me, I am new to code functions in Google Sheets.

Hoping folk can assist. I have been playing with a few function codes found online but cannot get them to do what I need, and am a complete novice at Google Apps Script.


The scenario; Sheet1 - contains data pulled from an API, for social media metrics. Column A holds the date of the social media post. The API only sends data for X days from the post being made, for this example let's say 90 days. Sheet2 - I therefore want a script that copies the entire row from Sheet1, when the date in Column A gets to X days from todays date. This then writes the row to Sheet2, under any previous rows that are there.


Notes; the row written to sheet 2 should contain values and number formats but not copy over formulas. I need to be able to edit the script to change the days value, e.g. from 90 days to 120 days, or similar


Please can someone help me with this code? 


Here are some snippets of code I found which I am trying to use as a base, but i really don't know how all of the code elements work, beyond the ones that refer to source sheet.


I found the following elements;


This one places a time and date stamp on my target sheet, randomly seems to do so at cell A61, and each time I run it adds the next row down A62 and so on - with time & date (formatted like this 1/20/2021 16:45:29);


function recordHistory() {



 
var ss = SpreadsheetApp.getActiveSpreadsheet();

 
var sheet = ss.getSheetByName("PIN ARCHIVE");

 
var source = sheet.getRange("A2:S2");

 
var values = source.getValues();

  values
[0][0] = new Date();

  sheet
.appendRow(values[0]);

};


 

Then I have this which when run doesn’t do anything I can see;


function moveRows() {



 
var ss=SpreadsheetApp.getActive();

 
var ssh=ss.getSheetByName('PINS');

 
var dsh=ss.getSheetByName('PIN ARCHIVE');

 
var srg=ssh.getDataRange();//You might want to specify a more unique range. This just gets all of the data on the sheet

 
var svA=srg.getValues();

 
var d=0;//deleted row counter

 
//I  assume one row for a header

 
for(var i=1;i<svA.length;i++) {

   
if(svA[i][0].toString().toLowerCase()=='true') {

      dsh
.appendRow(svA[i]);//append entire row to Sheet2

      ssh
.deleteRow(i-d+1);//accounts for the difference between length of array and number of remaining row.

      d
++;

   
}

 
}

}


 

Plus some code which I think I can add to one or both of these which returns the number of days since a given date

 

function daysSince(d) {



if (!d || typeof d.getTime != "function") return "";

return Math.round((new Date()-d)/(1000*60*60*24));

}


 

So I have pieces but no whole function that will achieve moving entire row data from sheet 1 to sheet 2, based on X days from today.


Thank you, Ian

Boris Baublys

unread,
Jan 21, 2021, 11:54:02 PM1/21/21
to Google Apps Script Community
Hello Ian.
The essence of the algorithm that can help you solve this problem may be that the time trigger will run the code every day that checks when the date in Column A gets to X days from todays date.
If on some day this condition is met, the code will copy this line to the right place.
I would suggest that you start by creating a spreadsheet with raw data and then share it with a group.

четверг, 21 января 2021 г. в 12:35:49 UTC+3, Ian Taylor:

Ed Sambuco

unread,
Jan 22, 2021, 10:43:57 PM1/22/21
to Google Apps Script Community
You need to do a couple things:

- First, to calculate X days from today"

var futureDate = new Date((new Date()),getTime()  + 24*60*60*1000*X);

- Then examine each row in Sheet1 for date match with futureDate in column A (make sure column is formatted as date)   use a for loop with (i = 2; i <= sheet1.getLastRow(); i ++)  assuming header row in i =1 row.  

- For each row match with getRange(row, 1) == futureDate, use the copyTo method of Range class:

var rangeToCopy = sheet1.getRange(row, 1, 1, sheet1.getLastColumn());
rangeToCopy
.copyTo(sheet2.getRange(getLastRow() + 1, 1, sheet1.getLastColumn());

Ed Sambuco

unread,
Jan 22, 2021, 10:54:32 PM1/22/21
to Google Apps Script Community
Oops a typo:

The last code line in my note should read:

rangeToCopy.copyTo(sheet2.getRange(getLastRow() + 1, 1, 1, sheet1.getLastColumn());

which tells us we want the row after last row in sheet2, starting from column 1, but just that row, for all columns in that row.  Sorry for any confusion.

On Thursday, January 21, 2021 at 4:35:49 AM UTC-5, Ian Taylor wrote:

Ed Sambuco

unread,
Jan 23, 2021, 12:10:04 AM1/23/21
to Google Apps Script Community
Ian -- my apologies, my previous posts really were not the right answer.  I am attaching a function below that works (i tested it to be sure)  You will have to run this as a standalone, either manually from the script editor or on a time trigger.
Again, sorry, I am kinda tired now and I answered you too quickly before:

function copyRow(futureDays) {
  // Get spreadsheet
  var workbook = SpreadsheetApp.openById(DriveApp.getFilesByName("Test0122").next().getId());
  var sheet1 = workbook.getSheetByName("Sheet1");
  var sheet2 = workbook.getSheetByName("Sheet2");
  for (i = 2i <= sheet1.getLastRow(); i++) {
    if (dateDiff(sheet1.getRange(i1).getValue(), new Date()) == futureDays) {
      var rangeToCopy = sheet1.getRange(i11sheet1.getLastColumn());
      rangeToCopy.copyTo(sheet2.getRange(sheet2.getLastRow() + 111sheet1.getLastColumn()));
    }
  }
}
function dateDiff(date1date2) {
  var diff = date1.getTime() - date2.getTime();
  return Math.floor(diff / (24 * 60 * 60 * 1000)) + 1;
} 

Obviously, you'll have to change spreadsheet name and maybe sheet names too.


On Thursday, January 21, 2021 at 4:35:49 AM UTC-5, Ian Taylor wrote:

Ian Taylor

unread,
Jan 25, 2021, 4:55:48 AM1/25/21
to Google Apps Script Community
Thanks to all for the responses over the weekend, now working through them.

Ian Taylor

unread,
Jan 25, 2021, 5:10:18 AM1/25/21
to Google Apps Script Community
So have placed the code into my sheet, clicked RUN and got;

10:01:50 AM
Error
Exception: Cannot retrieve the next object: iterator has reached the end.
copyRow
@ SOLUTION 1.gs:3

I also get this when trying to run just the data portion;

10:05:28 AM
Error
TypeError: Cannot read property 'getTime' of undefined
dateDiff
@ SOLUTION 1.gs:14

Wondering if it's because the data format in sheet1 has time also?
Here's the sheet, I've had to remove/replace some data as it's real client data, but the structure remains (social media data from Pinterest)


Adam Morris

unread,
Jan 25, 2021, 5:56:20 AM1/25/21
to google-apps-sc...@googlegroups.com
It sounds to me like this can be accomplished with just formulas. If you provide spreadsheet (preferably with dummy data) I’ll see if I can’t whip it up.



--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/13e0d8f2-264b-4a5c-9ebe-5bb58e1c4955o%40googlegroups.com.
--

Ian Taylor

unread,
Jan 25, 2021, 7:07:03 AM1/25/21
to Google Apps Script Community
Hi Adam, there was a link in my post previous to yours. Formula would be good, but key thing here is when I 'write to' the archive sheet (currently called Sheet2) I want a new row below the most recent, no data in the archive sheet should be over-written. I only say this as the complexity of it made me think a script would be apt. BUT if you know your formulas then I'm sure it's a fun challenge! My formula knowledge is better than script but falls down on more complex tasks. Thank you in advance sir.

Adam Morris

unread,
Jan 25, 2021, 8:40:32 AM1/25/21
to google-apps-sc...@googlegroups.com
Oh I see. So wait, a blank row that you can edit, or just like skipping a row that is blank. 

Probably you explained yourself above, sorry to ask more questions.

--

Ian Taylor

unread,
Jan 25, 2021, 9:26:15 AM1/25/21
to Google Apps Script Community
Hi Adam, Neither really - it's about taking an entire row from Sheet1 once it's X number of days from today (column A is the date), and putting all of the row onto the next BLANK row of Sheet2.

Ed Sambuco

unread,
Jan 25, 2021, 9:31:37 AM1/25/21
to Google Apps Script Community
Hmm ... Ian .. I retested the function on my own test spreadsheet and confirmed that it works.

I copied a subset of your spreadsheet into my account, and that one tested OK as well

A couple points.

- JS treats date-formatted sheets cells as having all zeroes in hour/minute/second/millisecond.  So, yes, for proper execution, your column A should be formatted as date, not date/time.  To preserver the time, add another column, copy your date/time from column A, then set ne column format to time.  Then back to column A and format as date.

- The function I sent you assumes that you are looking for dates X days into the future.  (I am comparing your column A date to new Date(), which is the current data/time, and adjusting for nonzero time portion of current date)  What forwrd date exactly are you looking for?  A date X days from some date in the past?a

On Thursday, January 21, 2021 at 4:35:49 AM UTC-5, Ian Taylor wrote:

Ian Taylor

unread,
Jan 25, 2021, 9:38:22 AM1/25/21
to Google Apps Script Community
Thanks Ed,

Few notes back;
- sheet1 is as pulled via an API call (using Supermetrics) - I may be able to have this populate from Cell B1 and add a new column A formatted as needed, as I think I'm at the behest of how the API (which I cannot tweak) exports the data
- what I need to do is look back, so today being 25 Jan, lets say the number of days back is 7, I'd need to schedule it to check each day and when it sees a row starting 18 Jan (7 days back) it will copy that row to the next blank row on sheet2.
(I chose 7 for this reply as it's faster than me working out 60 days back from 25 Jan :) )

It also might be a use case to NOT copy the whole row, and just (example) columns A-T (for example) of that row. So I may need one that does whole row and one that does certain columns (mentioning now as the morning's work has highlightw a second use case in our data).

Appreciate you sir,

Ian

Reply all
Reply to author
Forward
0 new messages