Transfer parent and child archived records to other table

[Peter_Bernsen]

I have an app in G-sheet that is getting very slow. Orders and order details.

There ar too many records in the parent table (field [Archive]=true) and many child tables. 

What is the best way in transfering approximate 80% of the archive-records from this parent and child tables. Is it better to do it instantly or per period, scheduled bot?
For these archived records I have to make a Archive-app.
Synching avg is 30s+ and some bots are very slow (more than 60-600sec).
There are automation errors.

[JSO]

In my opinion, I think it's better to make transfers by periods: from the oldest record to the day before the period you want to keep in the main application.
For this, instead of doing it from AppSheet, I would try to do it with a Google Script called from an AppSheet bot.

[Peter_Bernsen]

I tried it whith ChatGPT to make someting. There should be others who encounter the same and had to make a solution for this. See generated code:  

function transferData() { var sourceSheet = SpreadsheetApp.openById("SOURCE_SHEET_ID").getSheetByName("Sheet1"); // Retrieve the source sheet from the source spreadsheet using its ID and sheet name // Replace "SOURCE_SHEET_ID" with the ID of the source spreadsheet and "Sheet1" with the actual name of the source sheet var parentTargetSheet = SpreadsheetApp.openById("TARGET_SHEET_ID").getSheetByName("ParentSheet"); // Retrieve the parent records target sheet from the target spreadsheet using its ID and sheet name // Replace "TARGET_SHEET_ID" with the ID of the target spreadsheet and "ParentSheet" with the actual name of the parent records target sheet var childTargetSheet = SpreadsheetApp.openById("TARGET_SHEET_ID").getSheetByName("ChildSheet"); // Retrieve the child records target sheet from the target spreadsheet using its ID and sheet name // Replace "TARGET_SHEET_ID" with the ID of the target spreadsheet and "ChildSheet" with the actual name of the child records target sheet var sourceData = sourceSheet.getDataRange().getValues(); // Get the values from the entire data range of the source sheet and assign them to the "sourceData" variable var parentData = []; // Declare an empty array to store the filtered parent records var childData = []; // Declare an empty array to store the filtered child records var currentDate = new Date(); // Get the current date and time and assign it to the "currentDate" variable var oneYearAgo = new Date(currentDate.getFullYear() - 1, currentDate.getMonth(), currentDate.getDate()); // Calculate the date one year ago from the current date and assign it to the "oneYearAgo" variable for (var i = 0; i < sourceData.length; i++) { var rowData = sourceData[i]; // Get each row of data from the source sheet var date = new Date(rowData[0]); // Get the date value from the first column of the current row (assuming the date column is in the first column) if (date < oneYearAgo) { // Check if the date is older than one year ago parentData.push(rowData); // Add the current row of data to the parent data array childData.push.apply(childData, getChildRecords(sourceSheet, rowData[1])); // Get the child records related to the current parent record and add them to the child data array // Assuming the parent-child relationship is in the second column (column index 1) } } parentTargetSheet.getRange(1, 1, parentData.length, parentData[0].length).setValues(parentData); // Set the values of the parent data array to the target sheet for parent records childTargetSheet.getRange(1, 1, childData.length, childData[0].length).setValues(childData); // Set the values of the child data array to the target sheet for child records } function getChildRecords(sheet, parentRecordId) { var childData = []; // Declare an empty array to store the child records var data = sheet.getDataRange().getValues(); // Get the values from the entire data range of the sheet for (var i = 0; i < data.length; i++) { var rowData = data[i]; // Get each row of data from the sheet var parentId = rowData[1]; // Get the parent record ID from the

[AleksiAlkio]

Peter, would it be better to start using real db instead of gSheet? Please check your gChat 😉

[Peter_Bernsen]

Hi Aleksi, in Gmail chat I do not see your response 🤔

[Peter_Bernsen · Accepted solution]

Hi Aleksi,

Welcome back... happy to hear from you again!!!