google script with the CRUD functions needed
Budget: $10 – $30 USD
I have a system with customer files and a master file. I need just one function in the master file that will write/update/delete new entries when any edit is done in the customer files.
Here is the technical task;
Create a Google script onEdit function that will
1. add any new entries in the columns (A2:D) of the customer spreadsheets ('Customer1', 'Sheet1'; 'Customer2', Sheet1...), to the 'Master spreadsheet', 'Sheet1' , (B2:E)
2. check and update any existing entries based on the columns A and B, and if the entry already exists in both and A and B, just update the values in the columns C, D, E, if not update the whole row.
3. if the entry in the customer spreadsheets ('Customer1', 'Customer2', ...), columns B, C or D is deleted, remove the row in the 'Master spreadsheet' accordingly
4. Please note, the spreadsheets need to be declared by ID's and not names.
For your reference, here is a code that worked for me but
-this is created for two different sheets of the same spreadsheet while I need several source spreadsheets and one target spreadsheet.
-The column that is checked against in order to update the whole row is "A" while I need to change this to check both "A" and "B"
- The functionality for deleting a row if the entry is deleted in the columns B, C or D, is missing in the code.
function onEdit(onEdit) {
// 1. Retrieve values from the source and target sheets.
var ss = SpreadsheetApp.getActive();
var [srcSheet, targetSheet] = ['Source Sheet', 'Target Sheet'].map(s => ss.getSheetByName(s));
var [srcValues, targetValues] = [[srcSheet, "A2:K"], [targetSheet, "A2:K"]].map(s => s[0].getLastRow() == 1 ? [] : s[0].getRange(s[1] + s[0].getLastRow()).getValues());
// 2. Create objects for searching values of the column "A".
var [srcObj, targetObj] = [srcValues, targetValues].map(e => e.reduce((o, [a, ...b]) => (o[a] = b, o), {}));
// 3. Check update values at the target sheet.
var updatedValues = targetValues.map(([a, ...b]) => [a, ...(srcObj[a] || b)]);
// 4. Check append values.
var appendValues = srcValues.reduce((ar, [a, ...b]) => {
if (!targetObj[a]) ar.push([a, ...b]);
return ar;
}, []);
// 5. Update the target sheet.
var values = [...updatedValues, ...appendValues];
targetSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}
Here is the technical task;
Create a Google script onEdit function that will
1. add any new entries in the columns (A2:D) of the customer spreadsheets ('Customer1', 'Sheet1'; 'Customer2', Sheet1...), to the 'Master spreadsheet', 'Sheet1' , (B2:E)
2. check and update any existing entries based on the columns A and B, and if the entry already exists in both and A and B, just update the values in the columns C, D, E, if not update the whole row.
3. if the entry in the customer spreadsheets ('Customer1', 'Customer2', ...), columns B, C or D is deleted, remove the row in the 'Master spreadsheet' accordingly
4. Please note, the spreadsheets need to be declared by ID's and not names.
For your reference, here is a code that worked for me but
-this is created for two different sheets of the same spreadsheet while I need several source spreadsheets and one target spreadsheet.
-The column that is checked against in order to update the whole row is "A" while I need to change this to check both "A" and "B"
- The functionality for deleting a row if the entry is deleted in the columns B, C or D, is missing in the code.
function onEdit(onEdit) {
// 1. Retrieve values from the source and target sheets.
var ss = SpreadsheetApp.getActive();
var [srcSheet, targetSheet] = ['Source Sheet', 'Target Sheet'].map(s => ss.getSheetByName(s));
var [srcValues, targetValues] = [[srcSheet, "A2:K"], [targetSheet, "A2:K"]].map(s => s[0].getLastRow() == 1 ? [] : s[0].getRange(s[1] + s[0].getLastRow()).getValues());
// 2. Create objects for searching values of the column "A".
var [srcObj, targetObj] = [srcValues, targetValues].map(e => e.reduce((o, [a, ...b]) => (o[a] = b, o), {}));
// 3. Check update values at the target sheet.
var updatedValues = targetValues.map(([a, ...b]) => [a, ...(srcObj[a] || b)]);
// 4. Check append values.
var appendValues = srcValues.reduce((ar, [a, ...b]) => {
if (!targetObj[a]) ar.push([a, ...b]);
return ar;
}, []);
// 5. Update the target sheet.
var values = [...updatedValues, ...appendValues];
targetSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}