CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
shipment lda
shipment lda

Reputation: 39

Save import data in google sheet

I created two google sheet with an exchange of data from sheet 1 to 2 with the IMPORTRANGE function. I would like to save my data on the second sheet while importing. But I don't know how. Below is my code which allows me to delete the lines as soon as 30 days have passed.

function onEdit() {
  // Sheet the data is on.
  var SHEET = "Copie de TF VBX";
  // The value that will cause the row to hide.
  var VALUE = "date";
  // The column we will be using
  var COLUMN_NUMBER = 12
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var cell = ss.getActiveCell()
  var cellValue = cell.getValue();
  var colonne = cell.getColumn();
  var ligne = cell.getRow();
  var date2 = new Date(Date());
  var date1 = new Date(cellValue);
  // différence des heures
  var time_diff = date2.getTime() - date1.getTime();
  // différence de jours
  var days_Diff = time_diff / (1000 * 3600 * 24);

  //Ensure on correct sheet.
  if(SHEET == activeSheet.getName()){
  
    //Ensure we are looking at the correct column.
    if(colonne == COLUMN_NUMBER){
      //If the cell matched the value we require,hide the row.
      if(days_Diff>31){
        activeSheet.deleteRow(ligne)
        //Browser.msgBox(days_Diff)
      }; 
    };
  };
}

Thank you for your help.

Upvotes: 1

Views: 154

Answers (1)

Cooper
Cooper

Reputation: 64062

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() = "Copie de TF VBX" && e.range.columnStart == 12 && e.value) {
    const td = new Date()
    const thd = new Date(td.getFullYear(), td.getMonth(), td.getDate() - 31).valueOf();
    const date1v = new Date(e.value).valueOf();
    if (date1v > thd) {
      sh.deleteRow(e.range.rowStart);
      e.source.toast(DiffInDays(td,new Date(e.value));
    }
  }
}

function DiffInDays(Day1,Day2) {
  if(Day1 && Day2 && (Object.prototype.toString.call(Day1) === '[object Date]') && (Object.prototype.toString.call(Day2) === '[object Date]')) {
    var day=86400000;
    var t1=new Date(Day1).valueOf();
    var t2=new Date(Day2).valueOf();
    var d=Math.abs(t2-t1);
    var days=Math.floor(d/day); 
    //Logger.log(days);
    return days;
  } else {
    throw 'Invalid Inputs';
  }
}

Upvotes: 2

Related Questions