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
var rangeToCopy = sheet1.getRange(row, 1, 1, sheet1.getLastColumn());
rangeToCopy.copyTo(sheet2.getRange(getLastRow() + 1, 1, sheet1.getLastColumn());
rangeToCopy.copyTo(sheet2.getRange(getLastRow() + 1, 1, 1, sheet1.getLastColumn());
--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4f0af039-1a62-4672-b19d-770ea9ec3245n%40googlegroups.com.