CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
bobish
bobish

Reputation: 21

How to transfer a row to another tab in order to archive it in googlesheet

Quite new to JS, I'm willing to do some repetitive task within google sheet script editor.

The idea is to check all rows of tab1. For each row, if date in column D is older than 5 days prior to today, it transfers the full row to tab2 first empty row and make sure the row doesn't exist anymore in tab1. The idea is to archive data older than 5 days.

I could do this with VBA but I am very new to google sheet script and need the help of the community (I'm afraid I really don't have the basis of JS).

I will then add a time trigger (I know how to do this !) for this script.

EDIT:

from this script deleting :

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName("Foglio1");
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();
var oneweekago = new Date();
oneweekago.setDate(currentDate.getDate() - 7);

for (i=lastrow;i>=2;i--) {
  var tempdate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
  if(tempdate < oneweekago)   {   sheet.deleteRow(i); } } }

And this script moving data :

function moveCols() {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName('Sheet1');
  var destSheet = ss.getSheetByName('Sheet2');
  sourceSheet.getRange('A:A').moveTo(destSheet.getRange('G1'))
  sourceSheet.getRange('B:B').moveTo(destSheet.getRange('I1'))
  sourceSheet.getRange('C:C').moveTo(destSheet.getRange('L1')) }

I was able to do the below :

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("input");
var destSheet = ss.getSheetByName("input archive");
var datarange = sheet.getDataRange();
var lastrow = datarange.getLastRow();
var values = datarange.getValues();// get all data in a 2D array

var currentDate = new Date();
var oneweekago = new Date();
oneweekago.setDate(currentDate.getDate() - 7);

for (i=lastrow;i>=2;i--) {
  var tempdate = values[i-1][2];// arrays are 0 indexed so row1 = values[0] and col3 = [2]
  if(tempdate < oneweekago)   {  
    sourceSheet.getRange('i:i').moveTo(destSheet.getRange('LASTAVAILABLEROW'));
} } }

But I have no idea how to paste to last row. Maybe this will work ?

var Avals = destSheet.getRange("A1:A").getValues();
var Alast = Avals.filter(String).length;

Upvotes: 2

Views: 84

Answers (1)

James Sheard
James Sheard

Reputation: 139

Haven't got time to write a full answer but hope this gives you some guidance

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName("data");
var sheet2 = ss.getSheetByName("archive");

var data = sheet1.getRange("I1:I"+sheet.getLastRow()).getValues();
sheet2.getRange("A" + (sheet2.getLastRow()+1) + ":A" +sheet.getLastRow()).setValues(data);

Upvotes: 1

Related Questions