How to set up Two-Way Sync between Formaloo and Google Sheets

let formaloo={};function onOpen(){SpreadsheetApp.getUi().createMenu("🧡 Formaloo").addItem("Sync Form","showSyncForm").addToUi(),showSyncForm()}function showSyncForm(){var e=HtmlService.createHtmlOutputFromFile("formaloo").setTitle("Formaloo Two-Way Sync App");SpreadsheetApp.getUi().showSidebar(e)}function getSheetId(){return SpreadsheetApp.getActiveSpreadsheet().getId()}function getActiveSheet(){return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()}function getSheetTitle(){return getActiveSheet().getName()}function getSheetInfo(){return{sheetTitle:getSheetTitle(),sheetId:getSheetId()}}function rangesIntersect(e,t){e.getA1Notation(),t.getA1Notation();return e.getSheet().getSheetId()===t.getSheet().getSheetId()&&e.getRow()<=t.getLastRow()&&e.getLastRow()>=t.getRow()&&e.getColumn()<=t.getLastColumn()&&e.getLastColumn()>=t.getColumn()}function autoWidth(){var t=getActiveSheet(),e=t.getRange(1,1,1,t.getMaxColumns()).getValues()[0],o=["submitted at",formaloo.settings.slugColumnName],n=/^[a-zA-Z0-9\s]+\(.*\)$/,r=[];e.forEach(function(e,t){o.includes(e)&&r.push(t+1),n.test(e)&&r.push(t+1)}),r.forEach(function(e){t.autoResizeColumn(e)})}function cleanUpSheet(){var e=getActiveSheet(),o=e.getDataRange(),n=o.getValues();if(n.length<2)Logger.log("The sheet does not contain enough data.");else{var t=n[0],r=t.indexOf(formaloo.settings.slugColumnName),a=t.indexOf("submitted at");if(-1===r||-1===a)Logger.log(`One or both columns "${formaloo.settings.slugColumnName}" and "submitted at" do not exist.`);else{let t=0;for(let e=1;e<n.length;e++){var i=n[e][r],s=n[e][a];i&&s&&(t=e+1)}t<n.length&&e.getRange(t+1,1,n.length-t,o.getWidth()).clearContent()}}}function protectEntireSheet(){var e=getActiveSheet();e.protect().setDescription("Formaloo-Protection sheet protection").setWarningOnly(!0),Logger.log("Sheet protected: "+e.getName())}function protectColumns(){return formaloo.protectColumns()}function removeOldProtections(e=null,t="Formaloo-Protection"){return formaloo.removeOldProtections(e,t)}function removeEntireSheetProtection(){return formaloo.removeEntireSheetProtection()}function getVersion(){return formaloo.getVersion()}function getSettings(){return formaloo.settings}function resetStyles(){return formaloo.resetStyles()}formaloo.settings={appVersion:"2.17.3-beta",idColumnName:"#",slugColumnName:"Formaloo Record ID"},formaloo.getVersion=()=>formaloo.settings.appVersion,formaloo.resetStyles=()=>{var e=getActiveSheet(),t=e.getMaxRows(),o=e.getMaxColumns(),e=e.getRange(1,1,t,o);e.setBackground("#ffffff"),e.setFontColor("#000000"),e.setFontWeight("normal"),e.setFontSize(10),e.setFontFamily("Arial"),e.setBorder(!1,!1,!1,!1,!1,!1),e.setNumberFormat("@STRING@"),e.setHorizontalAlignment("left"),e.setVerticalAlignment("middle"),e.setWrap(!1),e.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP)},formaloo.applyDisableStyle=(e,t=!1)=>{e.setBackground("#fff9f4"),t?(e.setFontColor("#F76015"),e.setBackground("#fff9f4"),e.setFontWeight("bold")):e.setFontColor("#ffa776")},formaloo.protectColumns=()=>{var e=getActiveSheet(),t=e.getLastColumn(),o=e.getMaxRows(),t=(formaloo.removeOldProtections(e,"Formaloo-Protection"),formaloo.resetStyles(),e.getRange(1,1,1,t).getValues()[0]),n=t.indexOf(formaloo.settings.idColumnName)+1,r=t.indexOf(formaloo.settings.slugColumnName)+1,t=t.indexOf("submitted at")+1;0<r&&0<t?(n=e.getRange(1,n,o),formaloo.applyDisableStyle(n),n.protect().setDescription("Formaloo-Protection id protection").setWarningOnly(!0),n=e.getRange(1,r,o),formaloo.applyDisableStyle(n),n.protect().setDescription("Formaloo-Protection slug protection").setWarningOnly(!0),n=e.getRange(1,t,o),formaloo.applyDisableStyle(n),n.protect().setWarningOnly(!0).setDescription("Formaloo-Protection submitted at protection"),t=e.getRange(1,1,1,r),formaloo.applyDisableStyle(t,!0),t.protect().setWarningOnly(!0).setDescription("Formaloo-Protection header protection")):Logger.error(`Error: Columns "${formaloo.settings.slugColumnName}" or "submitted at" not found.`)},formaloo.removeOldProtections=(e=null,t="Formaloo-Protection")=>{(e=e||getActiveSheet()).getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function(e){console.log("%%%%%%%%%% protection.getDescription() >> ",e.getDescription()),e.getDescription().startsWith(t)&&e.remove()})},formaloo.removeEntireSheetProtection=()=>{getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.SHEET).forEach(function(e){e.getDescription().startsWith("Formaloo-Protection")&&e.remove()})};

<!doctype html><html lang="en"><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Formaloo</title><link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet"><link href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css" rel="stylesheet"><script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script><script src="https://unpkg.com/vue@3"></script><style>.container{max-width:300px;margin:0 auto}.progress{height:4px;background-color:#f3f3f3;margin:5px 0}.progress .indeterminate{background-color:#f76015;animation:indeterminate 2s infinite linear}@keyframes indeterminate{0%{left:-100%;width:100%}50%{left:100%;width:100%}100%{left:100%;width:100%}}.orange-btn{background-color:#f76015!important;color:#fff!important}footer{text-align:left;position:fixed;bottom:0;width:100%;font-size:.9rem;max-width:300px;margin:auto}footer .version{background-color:#ececec;width:100%;color:#666;padding:5px}a:visited,footer a{color:#f76015;text-decoration:underline}.warning-message{color:red;background-color:#ffe6e6;padding:1rem;border-radius:5px;margin-bottom:1rem}.warning-message li{text-align:justify;margin-bottom:.5rem}.button-group{display:inline-flex;flex-direction:row;align-items:center}.button-group button{margin-right:5px}.accordion-item{margin:3px 0;padding:1px;border:1px solid #e3e3e3;border-radius:5px}.accordion-header{padding:10px;cursor:pointer;background-color:#f9f9f9;transition:background-color .3s}.accordion-header:hover{background-color:#e0e0e0}.accordion-content{padding:0 10px;overflow:hidden;transition:max-height .3s ease;max-height:0}.accordion-content.active{max-height:300px}.accordion-content p{margin:10px 0}.faq-title{font-size:1.2rem;font-weight:700;color:#f3915b;padding:0;margin:0}@keyframes slideOut{0%{opacity:1;transform:translateX(0)}100%{opacity:0;transform:translateX(100%)}}.toast.slide-out{animation:slideOut .5s forwards}.sync-status{background-color:#fff7f5;border-radius:8px;padding:10px 20px 5px 20px}.sync-header{display:flex;align-items:center;margin-bottom:12px}.sync-title{font-size:1rem;color:#f3915b;font-weight:700}.sync-details{list-style-type:none;padding:0}.sync-details li{display:flex;align-items:center;margin:5px 0;justify-content:space-between}.sync-details strong{white-space:nowrap;margin-right:5px}.loader{border:2px solid transparent;border-radius:50%;border-top-color:#fff;width:20px;height:20px;animation:spin 1s linear infinite}@keyframes spin{to{transform:rotate(360deg)}}.text-success{color:green;font-weight:700}.text-danger{color:red;font-weight:700}.opacity{opacity:.5}.waiting-message{color:#b22222;background-color:#ffe6db;font-size:.9rem;font-weight:700;margin:7px auto;padding:5px 15px;border-radius:5px;text-align:left}</style><div id="app"><div class="container"><div class="row"><div class="col s12"><div class="header"><img src="https://cdn.prod.website-files.com/656ce8668244222553627f66/656ceeed63564c91d4c9c581_Formaloo%20navbar%20logo.svg" alt="Formaloo Logo" style="width:70%;height:auto;margin-top:1rem"></div></div></div></div><div class="row"><div class="col s12"><div class="button-group"><button class="btn orange-btn waves-effect waves-light" @click="submitForm" :disabled="isLoading" title="Sync data with Formaloo"><i class="material-icons left">sync</i> Sync</button> <button class="btn orange-btn waves-effect waves-light" @click="getSyncStatus" :disabled="getSyncStatusLoading" title="Get the latest sync status"><i class="material-icons" v-if="getSyncStatusLoading">hourglass_empty</i> <i class="material-icons" v-if="!getSyncStatusLoading">info</i></button> <button class="btn orange-btn waves-effect waves-light" @click="resetStyles" :disabled="isResettingStyle" title="Reformat the sheet"><i class="material-icons" v-if="isResettingStyle">hourglass_empty</i> <i class="material-icons" v-if="!isResettingStyle">imagesearch_roller</i></button></div></div><div class="col s12"><div class="waiting-message" v-if="isLoading || isResettingStyle || getSyncStatusLoading">Please wait, do not make changes.<div class="progress"><div class="indeterminate"></div></div></div></div></div><footer><div class="row"><div class="col s12"><div class="sync-status" v-if="syncStatus" :class="{'opacity': getSyncStatusLoading}"><div class="sync-header"><span class="sync-title" v-if="!getSyncStatusLoading">Latest Sync Status</span> <span class="sync-title" v-if="getSyncStatusLoading">Retrieving data ...</span></div><ul class="sync-details"><li><strong>Start Time: </strong><span>{{ formatDate(syncStatus.started_at) }}</span><li><strong>Stop Time: </strong><span>{{ syncStatus.stopped_at ? formatDate(syncStatus.stopped_at) : '-' }}</span><li><strong>Status: </strong><span :class="{
                                        'text-success': syncStatus.status === 'succeeded',
                                        'text-danger': syncStatus.status === 'failed'
                                }">{{ capitalizeFirstLetter(syncStatus.status) }} <span v-if="syncStatus.status === 'failed' && syncStatus.extra && syncStatus.extra.error"><i class="material-icons left" style="font-size:19px;margin-right:3px;cursor:pointer" :title="syncStatus.extra.error">info</i></span></span><li><strong>Duration: </strong><span v-if="syncStatus.duration > 0">{{ syncStatus.duration }} seconds</span> <span v-if="!syncStatus.duration">-</span><li><strong>Sheet Title:</strong> <span style="overflow:hidden;white-space:nowrap;text-overflow:ellipsis" :title="sheetTitle">{{ sheetTitle }}</span></ul></div></div></div><div class="row"><div class="col s12"><div class="faq-title">FAQ</div><hr style="border:1px solid #ffd3b7;margin:10px 0"><div v-for="(faq, index) in faqs" :key="index" class="accordion-item"><div class="accordion-header" @click="toggleAccordion(index)"><span v-html="faq.question"></span> <i class="material-icons right">{{ activeIndex === index ? 'expand_less' : 'expand_more' }}</i></div><div class="accordion-content" :class="{ active: activeIndex === index }"><p v-html="faq.answer"></div></div></div></div><div class="version"><strong>Version {{ appVersion }}</strong> | <a href="https://help.formaloo.com/" target="_blank">Support</a></div></footer></div><script>const env="prod";let apiKey=null,apiUrl=null;switch(env){case"local":apiKey="f0a5ce1ecc1fea87a57f06a52a8e12c48cb16d34",apiUrl="https://api.staging.formaloo.net";break;case"stage":apiKey="4638feb8378ac5799d6200889f97f2d3d88b9852",apiUrl="https://api.staging.formaloo.com";break;case"prod":apiKey="e2afd323b824a38e10a07d86ad5c527c2c007c0d",apiUrl="https://api.formaloo.me"}const faqs=[{question:'<strong style="color: #ff2f00; font-size: 1rem;">⛔ Essential Guidelines!</strong>',answer:'\n            <div style="color: #ff0000">\n                1- Do not change the column names.\n                <br>\n                2- Do not modify data in the <strong>"submitted at"</strong>, and <strong>"Formaloo Record ID"</strong>\n                columns.\n                <br>\n                3- Avoid adding any data in cells after the "Formaloo Record ID" column.\n                <br>\n                4- Please avoid from changing any data while syncing is in progress.\n            </div>\n        '},{question:"Sync the sheet's data.",answer:"\n            After you have changed your data in the Google Sheet, click the Sync button to store your\n            new changes in the corresponding form's data in Formaloo.\n        "},{question:"Delete row(s).",answer:'\n            1- Select the row(s) that you want to delete.\n            <br>\n            2- Right-click and select "Delete row(s)" from the context menu.\n            <br>\n            3- Click the Sync button and wait for the data to sync with your Formaloo data.\n        '},{question:"Check the latest sync status.",answer:"\n            You can click the info button (info icon) to get the latest status information from the last\n            sync you performed.\n        "}],app=Vue.createApp({data:()=>({sheetId:"",sheetTitle:"",isLoading:!1,isResettingStyle:!1,getSyncStatusLoading:!1,appVersion:"",activeIndex:0,syncStatus:null,faqs:faqs}),mounted(){google.script.run.withSuccessHandler((t=>{this.appVersion=t.appVersion})).getSettings(),this.getSheetInfo(),this.resetStyles()},methods:{getSheetInfo(t=()=>null){google.script.run.withSuccessHandler((({sheetId:e,sheetTitle:s})=>{this.sheetId=e,this.sheetTitle=s,t()})).withFailureHandler((t=>{console.error("Failed to fetch sheet info:",t)})).getSheetInfo()},showToast(t,e="green",s=1e4){M.toast({html:t,classes:e,displayLength:s,completeCallback:()=>{const t=document.querySelector(".toast");t&&(t.classList.add("slide-out"),setTimeout((()=>t.remove()),500))}})},handleErrors(t){t&&t.errors?(t.errors.general_errors?.length&&t.errors.general_errors.forEach((t=>{this.showToast(t,"red")})),t.errors.form_errors?.length&&t.errors.form_errors.forEach((t=>{this.showToast(t,"red")}))):this.showToast("An unknown error occurred.","red")},toggleAccordion(t){this.activeIndex=this.activeIndex===t?null:t},submitForm(){this.isLoading=!0,this.getSheetInfo((async()=>{if(!this.sheetId)return this.showToast("Sheet ID not found, please try again.","red"),void(this.isLoading=!1);setTimeout((()=>{this.getSyncStatus()}),1e3);const t={sheet_id:this.sheetId,sheet_title:this.sheetTitle};try{const e=await fetch(`${apiUrl}/v3/sync-with-gsheet/`,{method:"POST",headers:{"x-api-key":apiKey,"Content-Type":"application/json"},body:JSON.stringify(t)});if(e.ok){await e.json();this.showToast("Data synced successfully!","green"),this.resetStyles()}else{const t=await e.json();this.handleErrors(t)}}catch(t){console.error("Unexpected error:",t),this.showToast("An unexpected error occurred. Please try again later.","red")}finally{this.isLoading=!1,this.getSyncStatus(),google.script.run.cleanUpSheet(),google.script.run.protectColumns()}}))},formatDate(t){const e=new Date(t);return`${e.getFullYear()}-${String(e.getMonth()+1).padStart(2,"0")}-${String(e.getDate()).padStart(2,"0")} ${String(e.getHours()).padStart(2,"0")}:${String(e.getMinutes()).padStart(2,"0")}:${String(e.getSeconds()).padStart(2,"0")}`},capitalizeFirstLetter:t=>t?t.charAt(0).toUpperCase()+t.slice(1):"",getSyncStatus(){this.getSyncStatusLoading=!0,this.getSheetInfo((async()=>{const t=`${apiUrl}/v5/two-way-sync/google-sheets/${this.sheetId}/${this.sheetTitle}`;try{const e=await fetch(t,{method:"GET",headers:{"x-api-key":apiKey,"Content-Type":"application/json"}});if(e.ok){const t=await e.json();this.syncStatus=t||null}else{const t=await e.json();this.handleErrors(t),this.syncStatus=null}}catch(t){console.error("Unexpected error:",t),this.showToast("An unexpected error occurred. Please try again later.","red"),this.syncStatus=null}finally{this.getSyncStatusLoading=!1}}))},resetStyles(){this.isResettingStyle=!0,google.script.run.withSuccessHandler((()=>{this.isResettingStyle=!1})).protectColumns(),setTimeout((()=>{this.isResettingStyle=!1}),3e4)}}});app.mount("#app")</script>