How to prevent erase of cells in another Google Sheet file with Query/importrange

Question

I am trying to set up 2 google sheet files where File 1 takes in purchases for that day; the date, customer name, payment type, how much they paid, and what they paid for in a table. And File 2 will copy over what is in File 1 and basically acts as long term records for the month. I am using QUERY and IMPORTRANGE to import the data and also have it where File 2 will only copy if the date of the purchase is in that specific month.

My problem is if I erase anything in File 1 it will erase or change whatever is in File 2. So if we had 3 purchase today and I erase them at the end of the day, they will be erased in File 2 as well. I need help finding a way where File 2 will copy over the data but won't erase anything if File 1 erases data.

I've tried googling for my answer but I could not find one.

This is the formula I am using in File 2:

Logs is a named range that points wherein File 1 I want it to copy from

=QUERY((IMPORTRANGE("File 1 url (Erased for security reasons)", "Logs")),"select Col1,Col2,Col3,Col4,Col5, Col6,Col7 where Col1 >= date '"&TEXT(DATEVALUE("7/1/2019"),"yyyy-mm-dd")&"' and Col1 < date '"&TEXT(DATEVALUE("8/1/2019"),"yyyy-mm-dd")&"'",0)

to "archive" stuff, you will need a script. it's not about formulas. — player0
If I write a script can I still use the formula to import the data or do I need to write a script for that too? — DayDay
Do you know why your deleting data in File 2 when you're deleeting data in File1? Do you have any onEdit() functions running? — Cooper
@Cooper isn't cause it is basically mirroring whatever is in File 1? — DayDay
Is it because your importing everything from file1 into file2 on every edit? If so, it sounds like you may not really want to do that. — Cooper

I hope this is helpful to you I hope this is helpful to you · Accepted Answer · 2019-07-15T11:31:04

The problem is here is that your second sheet will update based on the running edit of the first sheet. If you want to keep the values in the archiving sheet then you need to copy the values witohut making a link between the cell you copied from.

You can achieve this with a small Apps Script Function which you can either from a bound script to one of the Spreadsheets, or a standalone script:

function copyFromOneSheetToAnother() {
  var sheet1 = SpreadsheetApp.openById("ID of Spreadsheet to copy from");
  var sheet2 = SpreadsheetApp.openById("ID of Spreadsheet to copy to");
  var currDate = new Date();
  var editedDate = new Date(currDate - 86400000);

  var insert = sheet1.getSheetByName("Name of sheet to copy from");
  var archive = sheet2.getSheetByName("Name of sheet to copy to");
  var insertData = insert.getRange(2, 1, (sheet1.getRange('A:A').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()), 28);

  for (row = 2; row <= insertData.getNumRows(); row++){
    var data = insert.getRange(row, 1, 1, 28).getValues();

    if (data[0][0] < currDate && data[0][0] > editedDate){
     archive.getRange((archive.getLastRow() + 1), 1, 1, 28).setValues(data).setNumberFormat("MM/dd/yyyy");
    }    
  }  
}

How to prevent erase of cells in another Google Sheet file with Query/importrange

1 Answers