Ditutup

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);

}

Kemahiran: JavaScript, Google Sheets

Tentang Klien:
( 0 ulasan ) Yerevan, Armenia

ID Projek: #34359278

10 pekerja bebas membida secara purata $24 untuk pekerjaan ini

(42 Ulasan)
5.4
(17 Ulasan)
5.2
heliosdhaka

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 Lagi

$20 USD dalam 7 hari
(8 Ulasan)
4.7
aruls1962

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 Lagi

$50 USD dalam 7 hari
(20 Ulasan)
4.4
dejanprogrammer

---------------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 Lagi

$20 USD dalam 7 hari
(2 Ulasan)
3.7
(5 Ulasan)
3.1
catlinnelken

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 Lagi

$20 USD dalam 3 hari
(3 Ulasan)
0.7
rfgnm3

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 Lagi

$20 USD dalam 7 hari
(0 Ulasan)
0.0
anilkr423975

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 Lagi

$10 USD dalam 7 hari
(0 Ulasan)
0.0
poyrazsever

EXCEL PROGRAMINI İYİ BİR ŞEKİLDE KULLANIP GÜN İÇERİSİNDE TAKİP SAĞLAYABİLİRİM. İYİ BİR ANALİZ YAPABİLİRİM

$10 USD dalam 7 hari
(0 Ulasan)
0.0