REPAIR or REBUILD: Syncronization of information from google sheets to google calendar with rules for scheduling Events

Taking as reference this video, the task is simple, but there are many rules to follow:

[login to view URL]

The main goal is to schedule alerts for making payments. Each Event represent a payment to be done. In some cases, one particular service must be paid in 2 payments, so 1 line may result in 2 Events.

For examle, with this given data:

[X] LSR/SERVICIO IN-OUT 13-17/04 Status Reserva Status Solicitud payment1 payment2 detail of payment

[0] Hotel Hallef, Natales 14-16-apr requested 28 pax 0

100% payment 1

[1] Hotel Camino 14-16-apr requested 7 pax 30 10

15% payment 1, 85% payment 2

>>>>>>>>>>>>>>>>>

"14-apr" under column "IN-OUT": the starting date of the service (tourism provider). Ignore range dates, only first date, so ignore "16". Calendar starting dato would be "14-apr-2023 00:00:00 -4"

"0", "30" and "10" under columns "Payment 1" and "payment 2": this column sets the number of days of anticipation that the service must be paid, in this case the first hotel has to be fully paid the same day of the service and de second hotel must be paid 15% in advanced 30 days and 85% with 10 days of advance of the service (still before the passangers arrive to the hotel).

So for the result In the Calendar Event, 3 events should be syncronized:

"Payment 1" , "14-apr-2023 00:00:00 -4" , "Hotel Hallef, Natales ; 100% payment 1"

"Payment 2" , "4-abr-2023 00:00:00 -4" , "Hotel Camino ; 15% payment1, 85% payment2"

"Payment 1" , "15-mar-2023 00:00:00 -4" , "Hotel Camino ; 15% payment1, 85% payment2"

The rest of the data is for internal use and not for creating the Events on calendar.

Criteria of Acceptance or Quality Checks:

1. Multile sheets: The script has to pull data from all of the available sheets (currently 10 but there will be more in the future).

2. Blanck rows: Used for separating some providers from others. The script must be able to ignore black rows and still syncronize all rows.

3. Multiple instances of the same Service: Each sheet is a trip but the same trip can be repeated over time, so the same 6 columns can repeat horizontally representing more services and for the matter, more payments to providers to be done.

4. This is a syncronization, so as the script will be running multiple times, we don't need to duplicate events. They should be removed before rescheduling, or ignore if an event already exists.

5. All payments to providers will be scheduled with 1 or 2 payments. the date of the payment is the [service date - payment anticipation]

5.1. If the resulting date is a holiday date or sunday, the payment or the Event must be advanced 1 day. This action can be repeated any number of days untill the day is not holiday or sunday.

5.2 The holyday calendar that must be used is the calendar of Chile, South America.

6. sendInvites: false so no mail is sent.

7. The date of the payment is often writen poorly so you must figure how to recognise the date. One option is with regular expresions: this one works fine: (\d{1,2})(\-|\/|\s)((\d{1,2})(\-|\/|\s))?(\w{1,12}) [login to view URL]

but there can be more efficient or easier ways.

8. When the background of a date is colored in red, #ff0000 , 255:0:0 , or "STATUS RESERVA" == "ANULADO", the Event must not be created.

9. Debe existir un botón llamado "Sincronizar Calendarios" que ejecute el Script asociuado al Google Sheets.

10. Blanck data is indicative that the ,

11. The Script must be able to finish procesing all cases so some degree of optimization may be needed.

12. Missing data on payment 1 and payment 2 is indicative that the service is payed the same day of the service and is only 1 payment.

13. The original work in Google Sheet and the original Script are in spanish so:

ene=jan

feb=feb

mar=mar

pago = payment

detalle=detail

Google Apps Scripts Google Sheets JavaScript

Nº del proyecto: #36297625

Sobre el proyecto