Design question: Archive old data to another sprea...

Lucinda_Mason · 08-17-2020 07:54 AM

I have two tables that will grow a lot. The old records, which meet certain criteria, will no longer be needed in my app.

Is it more efficient to:

use a workflow/report to copy those records to the Archive sheet?
use security filters to keep those records from being loaded in the app. Keeping them all in the original table.

Thanks.

Steve

Another possibility:

Lucinda_Mason

based on
Security Filters with Spreadsheet Data

When reading data from a spreadsheet source, the entire spreadsheet or worksheet is retrieved from the cloud backend before the security filter is applied. Therefore, this does not reduce the time needed for this initial step of the sync process. However, it can significantly reduce the time needed for the second step (sending the data to the app) and can significantly reduce the time and space needed for the third step (saving the data locally with the app).

It seems like moving the old data to another sheet would be best. I understand @tsuji_koichi concern with putting the data back together but in my case, for this app, that will not be needed.

Daisy_Ramirez

Hi Lucinda, hope you’re all safe and well. I’m running through the same scenario now and wondered if you developed an archive module of sorts within the app to mark your records “Archived” or if you’re doing it manually. Also are deleting the records manually from the google sheet or have you create a way to cleanly remove the records without leaving the spaces in the google sheet?

Thanks Lucinda

Thiago_Calcagni

@Daisy_Ramirez this might help you.

Daisy_Ramirez

Thanks Thiago! I was just reading through that link - Wow ! Exactly what I need!

Thanks again!

Lucinda_Mason

I have been using the Tip Thiago posted. I’ll post my code just in case I made any minor changes. I do have a header row.:

function deleteBlankRows() {

    var SS = SpreadsheetApp.getActiveSpreadsheet();

    // Get sheets
    var sheets = SS.getSheets();

     // Loop through sheets. Delete blank rows in each sheet.
    for (var s=0; s < sheets.length; s++) {
    var currentSheet = sheets[s];
    var sheetName = sheets[s].getName();
    var searchDataRange = currentSheet.getRange(1,1,currentSheet.getMaxRows(),currentSheet.getMaxColumns()); // get the ENTIRE sheet. not just where the data is.
    var searchValues = searchDataRange.getValues();
    var numRows = searchValues.length;
    var numCols = searchDataRange.getNumColumns();
    var rowsToDel = [];
    var delRow = -1;
    var prevDelRow = -2;
    var rowClear = false;
 
 // Loop through Rows in this sheet
 for (var r=2; r < numRows; r++) {
   
   // Loop through columns in this row
   for (var c=0; c < numCols; c++) {
     if (searchValues[r][c].toString().trim() === "") {
       rowClear = true;
     } else {
       rowClear = false;
       break;
     }
   }
   
   // If row is clear, add it to rowsToDel
   if (rowClear) {
     if (prevDelRow === r-1) {
       rowsToDel[delRow][1] = parseInt(rowsToDel[delRow][1]) + 1;
     } else {
       rowsToDel.push([[r+1],[1]]);
       delRow += 1;
     }
     prevDelRow = r;
   }
 }
 
 
 //Logger.log("numRows: " + numRows);
 //Logger.log("rowsToDel.length: " + rowsToDel.length);
 
 // Delete blank rows in this sheet, if we have rows to delete.
 if (rowsToDel.length>0) {
   // We need to make sure we don't delete all rows in the sheet. Sheets must have at least one row.
   if (numRows === rowsToDel[0][1]) {
     // This means the number of rows in the sheet (numRows) equals the number of rows to be deleted in the first set of rows to delete (rowsToDel[0][1]).
     // Delete all but the first row.
     if (numRows > 1) {
       currentSheet.deleteRows(2,numRows-1);
     }
   } else {
     // Go through each set of rows to delete them.
     var rowsToDeleteLen = rowsToDel.length;  
     for (var rowDel = rowsToDeleteLen-1; rowDel >= 0; rowDel--) {
       currentSheet.deleteRows(rowsToDel[rowDel][0],rowsToDel[rowDel][1]);
     }
   }
  }
 }
}

Daisy_Ramirez

Thanks Lucinda! I’m not too familiar with Google Scripts. Does the code check “all” worksheets in the spreadsheet or just one particular worksheet?

LeventK

@Daisy_Ramirez
It checks all the sheets in a spreadsheet. However provided sheets have too many rows of data and have a lot of sheets, the script will time out.

Lucinda_Mason

I’m well. Just very busy with non App related work so I haven’t been able to hang out here.

I use a separate app to run my archiving. That way the three, or so, archived tables don’t slow down the main app. In my case, I don’t ever foresee needing to put the data back together.

In the archive app, I link to the live tables I want to archive as well as the archived tables, which are stored in a separate spreadsheet.

I have a combo action that does it individual step. This action is called by a report which runs once a week. Since my archived records are being moved, then deleted from the live app, I don’t write anything to those records in the “From” table. When the record is copied to the archive table, I write Today() to the DateArchived field.

It’s been working for quite a while.

Daisy_Ramirez

This is my exact scenario. I have about 4 tables that will require this “clean up”. You mentioned you use Scheduled Reports to run the combo action but when I open Reports I don’t see “Change Data” option there. Are you on Pro?

Thanks so much Lucinda! Very helpful

Lucinda_Mason

Yes, I’m on Pro.

Daisy_Ramirez

Do you have the “Change Data” option?

Lucinda_Mason

Yes.

Steve

Daisy_Ramirez

Yep! That was it! Thanks Steve

Koichi_Tsuji

I would say the best practice is always to stay single table rather than splitting into multiple tables in light of consumption of those data whatever purpose afterwards. In case splitting into separate tables, you may see the difficulties to union/combine those separate ones when you need to use those.

Security filters should be the best tool to achieve this task.

This is just a sample workaround where Appsheet should provide bunch of different approaches though.

For instance, you place one physical record, such as “YYYYMM” as key to slice the table by security filter. Add expression to app formula or initial value something like text(Today(),“YYYYMM”)
By arranging this, app will automatically add yyyymm stampt to that field, which will works as reference point when, ie. which year and month the row was generated, and act as anchor to apply security filter.

Next, create the usersettings. This should be Enum type. Same name like YYYYMM. Value options you simple add the 202001, 202002, 202003… which stands for YYYYMM values. Or you create the independent table which store those yyyymm list of values and make the usersetting type to ref to refer to this table. Set the inital value to text(Today(),“YYYYMM”)

Apply the security filter to the original table with condition of [YYYYMM]=usersettings(YYYYMM)

Once the user sync the app, the table will be sliced to pass the set of row of “current month”, and the rest of rows should be ruled out.

If user like to view the old data, still they can do so by change the value of usersettings to the past month and year.

If you want to swith the change of row filter, such as “by quarter” then you simply twist this trick, using text(today(),yyyy&month(today)/3.0) something like that. The table should be refreshed by every quarter etc.

I m not testing this workaround, but should work.

Daisy_Ramirez

Thanks Levent. Kinda scary to run this for production

LeventK

Yeah but unfortunately the script that @Lucinda_Mason using (and proposed) is highly ineffective code to handle this.

Daisy_Ramirez

Hi Levent, I read through Lucinda’s scenario and it appears to be my exact scenario. My apps currently do not allow any deletes so will need to adjust. What do you believe prevents the code from being fully effective?

LeventK

@Daisy_Ramirez
@Lucinda_Mason
You can check it out from here:

Design question: Archive old data to another spreadsheet vs using security filter