Macro Google Sheet "Macro action change"

Question

I would need a change in how this macro works

function getDynamicRow(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('B.Wioski');
  var sourceRange = sheet.getRange('A1:F26500');
  var data = sourceRange.getValues(); // Array of arrays [[Row1],[Row1],[Row3]]

  // add data to next empty row in the static sheet. 
  var targetSheet = ss.getSheetByName('TW');
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
}

currently the macro places the entries one under the other and I would need it to place the entries to the right of the preview image below

illustrative photo

additionally, I would need a macro to delete data older than 5-7 days

Please provide the data in you example is a table so that we can copy and paste it. The solution you require is quite easy except for deleting data older that 5 days because there are no timestamps in the data. Where do you wish to put timestamps. — Cooper, Nov 29 '21 at 16:38
I was thinking to insert a function =IF(J2<>"";IF(J1="";TODAY();J1);"") — P G, Nov 30 '21 at 03:42

SputnikDrunk2 · Accepted Answer · 2021-12-03T20:00:33.027

Suggestion

Perhaps you can try this implementation below:

Script:

function getDynamicRow(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('B.Wioski');
  var sourceRange = sheet.getRange('A1:F'+sheet.getLastRow());
  var data = sourceRange.getValues(); // Array of arrays [[Row1],[Row1],[Row3]]
  data.push(["","","","","",new Date()]); //added a timestamp for reference on deleting older data

  // add data to next empty row in the static sheet. 
  var targetSheet = ss.getSheetByName('TW');
  var currentCol = targetSheet.getDataRange().getNumColumns();
  if(currentCol != 1)currentCol = currentCol+1;
  targetSheet.getRange(1, currentCol, data.length, data[0].length).setValues(data);
}

Sample Result:

After running the script once:

After running it again (so on & so forth) :

As for deleting data older than 5 - 7 days, here's my implementation that you can try:

Since I have added a timestamp on every copied data on sheet TW, you can refer to this sample script below:

UPDATED

Script:

function deleteOlderThan5To7Days(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('TW');
  var row = targetSheet.getDataRange().getLastRow();
  var col = targetSheet.getDataRange().getLastColumn();
  var data = targetSheet.getRange(row,1,1,col).getValues();

  for(x=0; x< data[0].length; x++){
    var curCol = x + 1;
    if(data[0][x] != ""){
      var getDate = Utilities.formatDate(new Date(data[0][x]), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "dd");
      var checkDays = getDate - new Date().getDate();
      if(new Date(data[0][x]).getMonth()+1 == new Date().getMonth()+1){ //If data timestamp is within the current month
          if(checkDays > 5){ //Check if the timestamp of the data is more than 5 - 7 days
            Logger.log("Current row date: "+data[0][x]+"\n"+"Found on column #"+curCol+ " is "+checkDays+" days older");
            Logger.log("Delete data column " + curCol);
            targetSheet.insertColumnsAfter(targetSheet.getLastColumn(),1); //add a blank column on every deletion
            targetSheet.deleteColumn(curCol); //delete the points column data
            return;
          }else{
            //Data timestamp is NOT older than 5-7 days");
          }
      }else{ //Data was copied last month or older that the current month
        Logger.log("Current row date: "+data[0][x]+"\n"+"Found on column #"+curCol+ " is old");
        Logger.log("Delete data column "+ curCol);
        targetSheet.insertColumnsAfter(targetSheet.getLastColumn(),1); //add a blank column on every deletion
        targetSheet.deleteColumn(curCol); //delete the points column data
        return;
      }
    }
  }
}

Sample Demonstration:

Let's say the first data was copied on November 2, 2021 or 11/2/2021 as seen here on the sample sheet on TW:

After running the sample function deleteOlderThan5To7Days, here's the result:

The execution logs for review:

NOTE:

Same removal process applies if ever the month is the same as the current month but older than 5-7 days.

i modified it for my needs but i need help now needs the first 5 column to be non-removable from column F is to be removed can you rewrite the code to remove only the date columns? — P G, Dec 02 '21 at 20:30
Thanks your response. Regarding the first 5 columns that you need to be non-removable, it is quite unclear to me. Just to clarify, you mean you only want to delete the column `Points` that has a timestamp date older than 5-7 days, correct? — SputnikDrunk2, Dec 02 '21 at 20:41
Yes, I am only interested in the Points column. LP columns, village name, X position, Y position and player ID are not variable — P G, Dec 02 '21 at 23:25
I have updated my script for `deleteOlderThan5To7Days` on my answer to only delete the `Points` column that has a timestamp date older than 5-7 days — SputnikDrunk2, Dec 03 '21 at 14:53
If we answered your question, you may want to click the accept button on the left (check icon). By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved & they'll easily see this post on their searches. — SputnikDrunk2, Dec 03 '21 at 14:55
now the problem is that days are not deleted from the current month, e.g. today is 07/12/2021 and not deleted on 12/01/2021 come "(checkDays> 3)" is set to 3 days, giving any date not from this month, e.g. 09/01/2021 even on 11/30/2021 the data is then deleted replacing "Date (date [0] [x]). getMonth () + 1" with "Date (date [0] [x]). getDay () + 1" removes any previous day ignoring the formula "(checkDays> 3)" — P G, Dec 07 '21 at 06:52

Macro Google Sheet "Macro action change"

1 Answers1

Suggestion

As for deleting data older than 5 - 7 days, here's my implementation that you can try:

UPDATED

NOTE: