CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptsourceforge-appscriptgoogle-forms-api
Shrisiva
Shrisiva

Reputation: 15

Archive rows to another tab greater than 3 years

Requirement is to archive all the rows older than 3 years and insert those rows into a different tab in the same spread sheet.

I have below code to archive rows to another tab where date is older than 3 years . The below code is deleting all the rows before 3 years in the source tab correctly ,but it is not inserting the deleted rows to the target tab (archived rows).please let me know how to fix this code


const ss = SpreadsheetApp.getActive()
const shSource = ss.getSheetByName('Incident Report Details')
const shTarget = ss.getSheetByName('Archived rows')

function myFunction() {
  const COL_DATE = 1
  const HEADER_HEIGHT = 1
  if(shSource.getLastRow()==HEADER_HEIGHT){
    return
  }
  const data = shSource.getRange(1+HEADER_HEIGHT,1,shSource.getLastRow()-HEADER_HEIGHT, shSource.getLastColumn())
  data.sort(COL_DATE) // prepare for deletion of continous rows
  const minDate = new Date()
  minDate.setMonth(minDate.getMonth()-36)

  
  const rowsToDelete = data.getValues().filter(row=>row[COL_DATE-1]<minDate)
  if(rowsToDelete.length>0){
    console.log(`Delete ${rowsToDelete.length} rows where date is before ${minDate}`)
    shTarget.insertRowsAfter(shTarget.getLastRow(), rowsToDelete.length)
    shTarget.getRange(shTarget.getLastRow()+1,1, rowsToDelete.length, rowsToDelete[0].length).setValues(rowsToDelete)
  } else{
    console.log(`No rows to delete`)
  }

  shSource.deleteRows(HEADER_HEIGHT+1, rowsToDelete.length)
}

Upvotes: 1

Views: 79

Answers (2)

Antonio
Antonio

Reputation: 99

Modification--

  1. Why do you use these lines? : const COL_DATE = 1 and const HEADER_HEIGHT = 1 If they are always equal to 1 then you should remove them to make the code more readable and simple.

  2. I would also advise you to use the getDataRange() method instead of getRange() if you need to move all the data.

  3. Also I don't understand why you use this : shTarget.insertRowsAfter(shTarget.getLastRow(), rowsToDelete.length) Because when data is inserted into the table, new rows are automatically added, so this row is unnecessary in your code.

You can try this modification of your code :

const shS = 'Incident Report Details';
const shT = 'Archived rows';
const ss = (name) => { return SpreadsheetApp.getActive().getSheetByName(name) }
const lr = (name) => { return ss(name).getLastRow() }
const lc = (name) => { return ss(name).getLastColumn() }

function myFunction() {
  if (lr(shS) == 1) {
    return
  }
  ss(shS).sort(1); // prepare for deletion of continous rows
  const minDate = new Date();
  minDate.setMonth(minDate.getMonth() - 36);

  const rowsTD = ss(shS).getRange(2, 1, lr(shS) - 1, lc(shS)).getValues().filter(row => row[0] < minDate);
  console.log(`rowsToDelete: ${rowsTD.length}`);

  if (rowsTD.length > 0) {
    console.log(`Delete ${rowsTD.length} rows where date is before ${minDate}`);
    ss(shT).getRange(lr(shT) + 1, 1, rowsTD.length, rowsTD[0].length).setValues(rowsTD);
    ss(shS).deleteRows(2, rowsTD.length)
  } else {
    console.log(`No rows to delete`)
  }
}

Upvotes: 0

ab.it.gcp
ab.it.gcp

Reputation: 334

Updated answer:

Your code is working fine for me. Just added the deleteRows statement inside IF.

const ss = SpreadsheetApp.getActive()
const shSource = ss.getSheetByName('Incident Report Details')
const shTarget = ss.getSheetByName('Archived rows')

function myFunction() {
  const COL_DATE = 1
  const HEADER_HEIGHT = 1
  if (shSource.getLastRow() == HEADER_HEIGHT) {
    return
  }
  const data = shSource.getRange(1 + HEADER_HEIGHT, 1, shSource.getLastRow() - HEADER_HEIGHT, shSource.getLastColumn())
  shSource.sort(COL_DATE) // prepare for deletion of continous rows
  const minDate = new Date()
  minDate.setMonth(minDate.getMonth() - 36)


  const rowsToDelete = data.getValues().filter(row => row[COL_DATE - 1] < minDate)
  console.log('rowsToDelete: ' + rowsToDelete.length);

  if (rowsToDelete.length > 0) {
    console.log(`Delete ${rowsToDelete.length} rows where date is before ${minDate}`)
    shTarget.insertRowsAfter(shTarget.getLastRow(), rowsToDelete.length)
    shTarget.getRange(shTarget.getLastRow() + 1, 1, rowsToDelete.length, rowsToDelete[0].length).setValues(rowsToDelete)
    shSource.deleteRows(HEADER_HEIGHT + 1, rowsToDelete.length)
  } else {
    console.log(`No rows to delete`)
  }
}

Earlier answer (Please ignore):

Add this line before deleting rows

SpreadsheetApp.flush();

Spreadsheet operations are sometimes bundled together to improve performance. However, sometimes you may want to make sure that all pending changes are made right away as a scriptYour code is executingworking fine for me.

Upvotes: 1

Related Questions