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

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, data is written in the TITLE.: "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). Each sheet is linked to a different Google Calendar. Calendar_ID is in cell A2 2. Blanck rows: Used for separating some providers from others. The script must be able to ignore black rows and still syncronize all rows and columns. 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. First colum is provider, then, It can have many columns: 6,12,18,24,... and so on with the data to sync. 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. Sync is performed at least 2 times a week but it can be synced as many times as the clien needs. 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, saturday or sunday, the payment or the Event must be advanced "x day" untill is a non-holiday non-saturday and non-sunday, so the payment is done earlier. 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 no payment must be scheduled, 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