Archiving Data

[Chris_Jeal]

Hi,
Would anybody know if there is a way to copy a row of data to another table?
I would like to archive my data into another table by using behavior and creating action buttons to do this.

I don’t want to store old data on the main worksheet the app is reading and updating as I’m gathering on average, 80 rows of data per day and this can build up and will more than likely slow my app down.

Any suggestions?

Thanks in advance,
Chris.

[Aleksi]

One workaround is if you create a scheduled report with a “Save file” option. With that option you can at least copy your old data to your account day by day.

[Phil]

The scheduled report could copy the old rows from the main table to the backup table and then delete the rows from the main table. This assumes that you have some way to identify the rows to be moved and deleted. The scheduled report could run daily, weekly, or monthly, depending on your needs.

[Stefan_QREW]

@Phil, A Scheduled report can actually write values from one table to another table?

What setup achieves this? Do you pass a list of rows into a webhook and have something like Zapier append the new records to an existing document?

[Phil]

I believe the following could work:

1. The scheduled report would invoke a webhook.
2. The webhook would invoke the AppSheet API to add a set of rows to the Backup table. (I don’t think you need to include Zapier in the process, but I guess you could if you wished.)
3. The webhook would have a JSON body template. The JSON body template “Rows” data would be constructed using a Select expression that chooses the records to copy. Each such record would result in a row in the body.
4. A second webhook could do the deletes using the same approach.When doing deletes, you only need to specify the deleted record’s key field in the rows data.

[PeakPerformance]

I am looking for an archive feature that does not leave blank rows in the sheet, does this solution achieve this?

[Chris_Jeal]

Hi @PeakPerformance,

The solution I went with was a webhook that moves rows on a certain trigger.

This does leave blank rows in my tables but I combat that by using a script within the table that at the moment is triggered manually but apparently we can use our apps now to trigger the scripts within the sheet.

The script I’m using is below, if you do manage to get it to work via appsheet please share.

/**

• Deletes rows in the active spreadsheet that contain 0 or
• a blank valuein column “C”.
• For more information on using the Spreadsheet API, see
• https://developers.google.com/apps-script/service_spreadsheet
  */
  function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[2] == 0 || row[2] == ‘’) {
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};

/**

• Adds a custom menu to the active spreadsheet, containing a single menu item
• for invoking the readRows() function specified above.
• The onOpen() function, when defined, is automatically invoked whenever the
• spreadsheet is opened.
• For more information on using the Spreadsheet API, see
• https://developers.google.com/apps-script/service_spreadsheet
  */
  function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
  name : “Tidy Up”,
  functionName : “readRows”
  }];
  sheet.addMenu(“Empty Rows”, entries);
  };

Good Luck,
Chris.