Đã Đóng

google script with the CRUD functions needed

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 = [login to view URL]();

var [srcSheet, targetSheet] = ['Source Sheet', 'Target Sheet'].map(s => [login to view URL](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 => [login to view URL]((o, [a, ...b]) => (o[a] = b, o), {}));

// 3. Check update values at the target sheet.

var updatedValues = [login to view URL](([a, ...b]) => [a, ...(srcObj[a] || b)]);

// 4. Check append values.

var appendValues = [login to view URL]((ar, [a, ...b]) => {

if (!targetObj[a]) [login to view URL]([a, ...b]);

return ar;

}, []);

// 5. Update the target sheet.

var values = [...updatedValues, ...appendValues];

[login to view URL](2, 1, [login to view URL], values[0].length).setValues(values);


Kĩ năng: JavaScript, Google Sheets

Về khách hàng:
( 0 nhận xét ) Yerevan, Armenia

ID dự án: #34359278

10 freelancer chào giá trung bình$24 cho công việc này

(42 Nhận xét)
(17 Nhận xét)

Dear sir, I am 5+ years of experienced full-stack Django developer. Very high-level experience in Django ORM and its related features. also good at celery, wagtail, drf, sqlalchemey and lots more different features. I Thêm

$20 USD trong 7 ngày
(8 Nhận xét)

I’m a professional Engineer who has spent the past 7 years building, developing, and perfecting systems (from the ground up) through Google Sheets. With over 30 years of experience in the industry, I’ve not only learne Thêm

$50 USD trong 7 ngày
(20 Nhận xét)

---------------Javascript Expert-------------- Hi. Hope you are doing well. After looking at your job post, I feel I can be the best fit for this position. I would be interested to work with you as it perfectly matches Thêm

$20 USD trong 7 ngày
(2 Nhận xét)
(5 Nhận xét)

Dear employer, My name is Kenneth from Kenya and I would like to apply for your recently posted job. I am delighted to tell you that my qualifications are well matched with your needs and I consider myself worthy enou Thêm

$20 USD trong 3 ngày
(3 Nhận xét)

I can gurantee for good product. Hey I'm interested in your project, I have read out your requirements. We have 5+ years experience .We have worked on similar Projects to What You are Looking for. We Have A Variety of Thêm

$20 USD trong 7 ngày
(0 Nhận xét)

I can gurantree for good product. Hey I'm interested in your project, I have read out your requirements. We have 5+ year experience. We have worked on similar projects to What You are looking for. We Have A Variety of Thêm

$10 USD trong 7 ngày
(0 Nhận xét)


$10 USD trong 7 ngày
(0 Nhận xét)