hi guys , i have a problem whenever i try to export my data from tiny db to google spreadsheet , not all of the data is being export and some end up repeat the same data below here is my aia file and also my spreadsheet code , sorry i’m still newbie. QR_Scanner4.aia (85.6 KB)
function doPost(e) {
return insertData(e);
}
function doGet(e) {
return insertData(e);
}
function insertData(e) {
var ss = SpreadsheetApp.getActive();
var treeID = (e.parameter["Tree_ID"] || "").trim();
var fieldNumber = (e.parameter["Field_Number"] || "").trim();
var trialNO = (e.parameter["Trial_Number"] || "").trim();
var noBrunches = (e.parameter["NO_Brunches"] || "").trim();
var vacancy = (e.parameter["Vacancy"] || "").trim();
var rotten = (e.parameter["Rotten"] || "").trim();
var date = (e.parameter["Date"] || "").trim();
var timeScanned = (e.parameter["time_scanned"] || "").trim();
// Validate required parameters
if (!treeID || !fieldNumber || !trialNO || !noBrunches || !date || !timeScanned) {
return ContentService.createTextOutput("Missing required parameters")
.setMimeType(ContentService.MimeType.TEXT);
}
var lock = LockService.getScriptLock();
try {
lock.waitLock(30000);
// Check if the sheet for the given date exists, if not create it
var sheetName = date.replace(/\//g, "_");
var sh = ss.getSheetByName(sheetName);
if (!sh) {
sh = ss.insertSheet(sheetName);
sh.appendRow(["Tree_ID", "Field_Number", "Trial_Number", "NO_Brunches", "Vacancy", "Rotten", "Date", "time_scanned"]);
}
// Get the number of rows from a parameter (assuming all parameters have the same length)
var numRows = e.parameter["Tree_ID"].length;
// Build the incomingData array dynamically
var incomingData = [];
for (var i = 0; i < numRows; i++) {
incomingData.push([
e.parameter["Tree_ID"][i] || "",
e.parameter["Field_Number"][i] || "",
e.parameter["Trial_Number"][i] || "",
e.parameter["NO_Brunches"][i] || "",
e.parameter["Vacancy"][i] || "",
e.parameter["Rotten"][i] || "",
e.parameter["Date"][i] || "",
e.parameter["time_scanned"][i] || ""
]);
}
// Append ALL incoming data to the sheet
if (incomingData.length > 0) {
sh.getRange(sh.getLastRow() + 1, 1, incomingData.length, incomingData[0].length)
.setValues(incomingData);
}
// Sort the data in the sheet (optional)
var lastRow = sh.getLastRow();
if (lastRow > 1) {
sh.getRange(2, 1, lastRow - 1, sh.getLastColumn()).sort({ column: 8, ascending: true });
}
Logger.log("Data processed successfully.");
return ContentService.createTextOutput("Data processed successfully.")
.setMimeType(ContentService.MimeType.TEXT);
} catch (error) {
// Improved Error Handling
Logger.log("Error: " + error.message + " Stack trace: " + error.stack);
var errorMessage = "An error occurred: " + error.message;
if (error.name === "QuotaExceededException") {
errorMessage += " (Possible quota exceeded. Please try again later.)";
} else if (error.name === "Exception") {
errorMessage += " (Check script permissions and data format.)";
}
return ContentService.createTextOutput(errorMessage)
.setMimeType(ContentService.MimeType.TEXT)
.setStatusCode(500);
} finally {
if (lock.hasLock()) {
lock.releaseLock();
}
}
}
```
check your app… if you are using loop to upload data in gsheet then thats the reason…
use timer instead with 1000ms and upload data 1 by 1 after 1000ms
i have been trying to do it in the script , but i could not identify some data that being inputed by the app, i been using some multiple ai’s to identify the issue in the code but unable to do so… here’s my latest app script that i been testing yet it still have duplication issue
function insertData(e) {
var ss = SpreadsheetApp.getActive();
var treeID = e.parameter["Tree_ID"];
var fieldNumber = e.parameter["Field_Number"];
var trialNO = e.parameter["Trial_Number"];
var noBrunches = e.parameter["NO_Brunches"];
var vacancy = e.parameter["Vacancy"];
var rotten = e.parameter["Rotten"];
var date = e.parameter["Date"];
var timeScanned = e.parameter["time_scanned"];
// Convert single values to arrays if they aren't arrays
if (!Array.isArray(treeID)) treeID = [treeID];
if (!Array.isArray(fieldNumber)) fieldNumber = [fieldNumber];
if (!Array.isArray(trialNO)) trialNO = [trialNO];
if (!Array.isArray(noBrunches)) noBrunches = [noBrunches];
if (!Array.isArray(vacancy)) vacancy = [vacancy];
if (!Array.isArray(rotten)) rotten = [rotten];
if (!Array.isArray(date)) date = [date];
if (!Array.isArray(timeScanned)) timeScanned = [timeScanned];
// Validate required parameters
if (!treeID[0] || !fieldNumber[0] || !trialNO[0] || !noBrunches[0] || !date[0] || !timeScanned[0]) {
return ContentService.createTextOutput("Missing required parameters")
.setMimeType(ContentService.MimeType.TEXT);
}
var lock = LockService.getScriptLock();
try {
lock.waitLock(30000);
// Create a proper sheet name from the date
var dateString = new Date(date[0]).toISOString().slice(0, 10).replace(/-/g, "_");
var sh = getOrCreateSheet(ss, dateString);
var header = ["Tree_ID", "Field_Number", "Trial_Number", "NO_Brunches", "Vacancy", "Rotten", "Date", "time_scanned"];
// Ensure header exists
var firstRow = sh.getRange(1, 1, 1, header.length).getValues()[0];
if (firstRow.join("") !== header.join("")) {
sh.insertRowBefore(1);
sh.getRange(1, 1, 1, header.length).setValues([header]);
}
// Retrieve existing data to check for duplicates
var existingData = sh.getRange(2, 1, sh.getLastRow() - 1, header.length).getValues();
var existingKeys = new Set(existingData.map(row => row.slice(0, 7).join('|'))); // Existing keys from first 7 columns
var numRows = treeID.length;
var newRows = [];
for (var i = 0; i < numRows; i++) {
var newRow = [
treeID[i] || "",
fieldNumber[i] || "",
trialNO[i] || "",
noBrunches[i] || "",
vacancy[i] || "",
rotten[i] || "",
date[i] || "",
timeScanned[i] || ""
];
var key = newRow.slice(0, 7).join('|'); // Unique key based on the first 7 columns
if (!existingKeys.has(key)) {
newRows.push(newRow); // Only add rows that aren't duplicates
existingKeys.add(key); // Update existing keys set
}
}
if (newRows.length > 0) {
// Append only new unique rows
sh.getRange(sh.getLastRow() + 1, 1, newRows.length, newRows[0].length).setValues(newRows);
}
Logger.log(dateString + " sheet updated. " + newRows.length + " new unique rows added.");
return ContentService.createTextOutput("Data processed successfully. " + newRows.length + " new unique rows added.")
.setMimeType(ContentService.MimeType.TEXT);
} catch (error) {
Logger.log("Error: " + error.message + " Stack trace: " + error.stack);
return ContentService.createTextOutput("An error occurred: " + error.message)
.setMimeType(ContentService.MimeType.TEXT)
.setStatusCode(500);
} finally {
if (lock.hasLock()) {
lock.releaseLock();
}
}
}
function getOrCreateSheet(ss, sheetName) {
var sh = ss.getSheetByName(sheetName);
if (!sh) {
sh = ss.insertSheet(sheetName);
Logger.log(sheetName + " sheet created.");
}
return sh;
}
The purpose for the script is to handle any duplicated data and to create new sheets based on the date of the code. currently the new sheet creation is working fine but having trouble in duplication handling
no you have mistaken, for example if the data is created on 25.5.2024 it will create the shett based on that date and sorts all data to the date sheet. for the duplicated data it handles duplicated data per sheet for example tree id 1, and the second row is tree id 1. we want to avoid that… here’s the screenshot i have send below…as the screenshot below I want to avoid duplication like the tree id 456
fault occurs in your blocks…
you are uploading all data 2 times…
when condition=1 and also when condition=2 on both condition you uplaoding whole data…
Now how to fix
just upload 1st row data when condition=1
for 2nd row data then use condition=2
