Two-way sync of forms with Google Sheets helps you automatically update data in two systems: your form and your sheet in real time.
Prerequisites
Before getting started, make sure you have:
Two files for Google Sheets:
formaloo.min.js(JavaScript file provided by Formaloo) andformaloo.html(HTML file provided by Formaloo).Two-way sync integration is enabled between your Google spreadsheet and Formaloo form.
Synced your form with Google Sheets at least once.
To start, open your Formaloo dashboard, and follow these step-by-step instructions to continue:
Step 1: Sync your form with the Google Sheet
Click on the profile button and select "App Store". Now click on "Google Sheets" and choose the base form that you want to sync with Google Sheets.
📝 Additional insights:
Step-by-step guide on How to connect Google Sheets to your form.
Then enable the “Two-way Formaloo – Google Sheet sync” option on the same pop-up window.
Now, add the "Sheet title" and "Sheet URL" and click the "Connect" button.
⭐ Pro tip: Don't forget to "Sync" your form at the end.
To do this, click "Active integrations" and "Resync data".
Step 2: Prepare the Google Sheet
Open the Google Sheets file where you want to set up the Formaloo two-way sync.
Step 3: Open Google Apps Script
In Google Sheets, go to Extensions > Apps Script.
Step 4: Add the JavaScript file
Click the “+” icon in the top left corner and select Script.
Name the file formaloo.js.
Copy the contents of
formaloo.min.jsand paste it into this file.
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 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],r=/^[a-zA-Z0-9\s]+\(.*\)$/,n=[];e.forEach(function(e,t){o.includes(e)&&n.push(t+1),r.test(e)&&n.push(t+1)}),n.forEach(function(e){t.autoResizeColumn(e)})}function cleanUpSheet(){var e=getActiveSheet(),o=e.getDataRange(),r=o.getValues();if(r.length<2)Logger.log("The sheet does not contain enough data.");else{var t=r[0],n=t.indexOf(formaloo.settings.slugColumnName),a=t.indexOf("submitted at");if(-1===n||-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<r.length;e++){var s=r[e][n],i=r[e][a];s&&i&&(t=e+1)}t<r.length&&e.getRange(t+1,1,r.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.15.0-beta",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,t=getActiveSheet(),o=t.getLastColumn(),r=t.getMaxRows(),o=(formaloo.removeOldProtections(t,"Formaloo-Protection"),formaloo.resetStyles(),t.getRange(1,1,1,o).getValues()[0]),n=o.indexOf(formaloo.settings.slugColumnName)+1,o=o.indexOf("submitted at")+1;0<n&&0<o?(e=t.getRange(1,n,r),formaloo.applyDisableStyle(e),e.protect().setDescription("Formaloo-Protection slug protection").setWarningOnly(!0),e=t.getRange(1,o,r),formaloo.applyDisableStyle(e),e.protect().setWarningOnly(!0).setDescription("Formaloo-Protection submitted at protection"),o=t.getRange(1,1,1,n),formaloo.applyDisableStyle(o,!0),o.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()})};You can also access the code from this link: JavaScript Code.
Click Save to save your changes.
Step 5: Add the HTML file
Click the “+” icon again, this time selecting HTML.
Name this file formaloo.
Copy the contents of formaloo.html and paste it into this file.
<!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}.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>Started At: </strong><span>{{ formatDate(syncStatus.started_at) }}</span><li><strong>Stopped At: </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></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 apiKey="e2afd323b824a38e10a07d86ad5c527c2c007c0d",apiUrl="https://api.formaloo.me",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:"",isLoading:!1,isResettingStyle:!1,getSyncStatusLoading:!1,appVersion:"",activeIndex:0,syncStatus:null,faqs:faqs}),mounted(){google.script.run.withSuccessHandler((t=>{this.appVersion=t.appVersion})).getSettings(),google.script.run.withSuccessHandler((t=>{this.sheetId=t,this.getSyncStatus()})).getSheetId(),this.resetStyles()},methods:{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))}})},toggleAccordion(t){this.activeIndex=this.activeIndex===t?null:t},submitForm(){if(!this.sheetId)return void this.showToast("Sheet ID not found, please try again.","red");setTimeout((()=>{this.getSyncStatus()}),1e3),this.isLoading=!0;const t={sheet_id:this.sheetId};fetch(`${apiUrl}/v3/sync-with-gsheet/`,{method:"POST",headers:{"x-api-key":apiKey,"Content-Type":"application/json"},body:JSON.stringify(t)}).then((t=>t.ok?(this.resetStyles(),t.json()):t.json().then((t=>{throw t})))).then((t=>{this.isLoading=!1,this.showToast("Data synced successfully!","green")})).catch((t=>{this.isLoading=!1,t.errors&&t.errors.general_errors&&t.errors.general_errors.length&&t.errors.general_errors.forEach((t=>{this.showToast(`${t}`,"red")})),t.errors&&t.errors.form_errors&&t.errors.form_errors.length&&t.errors.form_errors.forEach((t=>{this.showToast(`${t}`,"red")}))})).finally((()=>{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;const t=`${apiUrl}/v5/two-way-sync/google-sheets/${this.sheetId}/`;fetch(t,{method:"GET",headers:{"x-api-key":"f0a5ce1ecc1fea87a57f06a52a8e12c48cb16d34","Content-Type":"application/json"}}).then((t=>t.ok?t.json():t.json().then((t=>{throw t})))).then((t=>{this.syncStatus=t||null})).catch((t=>{t.errors&&t.errors.general_errors&&t.errors.general_errors.length&&t.errors.general_errors.forEach((t=>{this.showToast(`${t}`,"red")})),t.errors&&t.errors.form_errors&&t.errors.form_errors.length&&t.errors.form_errors.forEach((t=>{this.showToast(`${t}`,"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>You can also access the code from this link: HTML Code.
Save the changes.
Step 6: Save the project
Click the Save icon at the top of the Apps Script editor.
(Optional) Rename the project to Formaloo App for easy reference.
Step 7: Refresh your Google Sheet
Go back to your Google Sheets file and refresh the page.
After refreshing, you should see a new menu item named Formaloo at the top of the screen.
Step 8: Authorize the Formaloo app
Click on Formaloo in the menu bar and select Sync Form.
A Google Authorization prompt will appear. Click OK to grant the necessary permissions.
Step 9: Access the Formaloo sidebar
Once authorized, you will see the Formaloo sidebar on the right side of your Google Sheets.
Step 10: Start syncing!
Now, with the Formaloo app added to your Google Sheets, you can update your form data in real-time, directly from Google Sheets.
⭐ Important tip for using Two-Way sync
Data-Only sync: This sync feature applies to data only. If you add a new column to your Google Sheets, it will not create a new field in your form automatically.
Example: Adding a new column
Add a new field to your form in Formaloo, then go to Google Sheets and you can see the new column with its slug.
You’re all set! With these steps completed, any updates in your Google Sheet will automatically reflect in your Formaloo form.
⛔ Essential Guidelines!
To ensure a smooth syncing process, please follow these guidelines:
Do Not change column names: The column names should remain exactly as they are set up. Renaming columns may disrupt the sync.
Do Not modify certain columns: Avoid editing data in the "submitted at" and "Formaloo Record ID" columns, as these are essential for the sync process.
Avoid adding data beyond the "Formaloo Record ID" column: Additional data entered beyond this column may not sync correctly and could interfere with data updates.
Do Not change data during syncing: While syncing is in progress, avoid making any changes to the sheet to prevent potential errors.
Sync the sheet's data
To update changes made in Google Sheets to your Formaloo form:
After making edits in Google Sheets, click the Sync button in the Formaloo sidebar.
Wait for the sync process to complete. This will update your changes in the corresponding Formaloo form.
Reformat the Sheet
To keep your Google Sheet organized, you can reformat it freely without triggering a sync.
Why reformat?: When data or fields in your Formaloo form change, these updates are applied to your Google Sheet. However, since Formaloo’s backend code doesn’t have access to Google Sheets’ formatting options, this can sometimes lead to a disorganized sheet layout.
Solution: Use the Reformat button in the Formaloo sidebar. This allows you to manually apply a consistent style to your sheet, ensuring it stays clear and easy to read without affecting the sync process.
Check the latest sync status
To view the status of your latest sync:
Click the info icon in the Formaloo sidebar. This will display the most recent sync details, so you can confirm that changes were applied successfully.
Delete a field in the Formaloo form
If you delete a field in your Formaloo form, you’ll need to manually sync it in Google Sheets for the change to be reflected. Unlike adding or editing fields, deletions do not sync automatically.
Delete the field: Go to your Formaloo form and delete the field you no longer need.
Sync with Google Sheets: After deleting the field, go to your connected Google Sheet.
Manually Sync the Sheet: Click the Sync button in the Formaloo sidebar to apply the changes. This will remove the deleted field data from your Google Sheet.
Delete a row in Google Sheets
If you want to delete specific rows of data in Google Sheets, follow these steps:
Select the row (s) you want to delete directly from Google Sheets.
Right-click and choose Delete row(s) from the context menu.
Click the Sync button: In the Formaloo sidebar, click Sync and wait for the process to complete. This will ensure that the deleted rows are also removed from your Formaloo data.
🗄️ Old dashboard:
Open your old Formaloo dashboard, and right-click your formaloo. Then select “View responses” and go to the “Integrations and webhooks” tab. Follow these steps to continue:
1- Sync your form with the Google Sheet
Click on the “Connect to Google Sheets” button. Sync your form with the sheet you want and save changes.
Then enable the “Two-way Formaloo – Google Sheet sync” option on the same pop-up window.
2-Add a trigger on Google sheet
Now you should set a script on the connected Google sheet and add a trigger for it.
Open your sheet, and on the menu under the “Extensions” menu item, choose the “Apps Script” option.
Then, delete everything from the opened editor and paste the following script instead:
function formalooSyncOnEdit(e) {
var range = e.range;
var spreadSheet = e.source;
var column = range.getColumn();
var row = range.getRow();
const url = "https://api.formaloo.net/v3/sync-with-gsheet/" const headers = { "x-api-key": "e2afd323b824a38e10a07d86ad5c527c2c007c0d" };
const formData = { "row": row, "column": column, "sheet_id": spreadSheet.getId() }; const options = { 'method': 'post', 'contentType': 'application/json', 'payload': JSON.stringify(formData), 'headers': headers }; UrlFetchApp.fetch(url, options);
}
Save changes from the save icon on the menu above the editor. After saving the function, go to the triggers part, and add a new trigger.
On the opened pop-up, change the “Select event type” field to “On edit”. Also, change the “Failure notification settings” field’s value to “Notify me immediately”. Then save the trigger.
After saving, it asks you to give access to the trigger so it can implement the updates. Give the access, and you’re done!
Once two-way sync is enabled, any changes made to the Google Sheet will be reflected in the form.
For example, if a row is deleted from the sheet, the corresponding response will be removed from the form. Similarly, if a value is changed in the sheet, the corresponding value in the form will be updated.
⭐ Pro tip:
When two-way syncing your form with Google Sheets, it’s important to note that it syncs the data, not the fields.
The two-way sync only applies to the data. If you add a new column to Google Sheets, it won’t automatically add a new field to your form. Therefore, changing the format of your Google Sheet or the form will affect the two-way sync’s performance.
So whenever you add a new column to your Sheet, ensure it’s added to your form as well. Also, if you add a new field to your form, make sure it exists on your Google Sheets as well. Read on to learn how you can do it.
How to add a new column/field in two-way sync
If you decide to add a new column to your Google Sheet or add a new field to your form, in order for your two-way sync to work, you should make sure it is applied to both parties.
First, create the field on your form and save the changes. Then create a new column after the last column of your Google Sheet and put the slug of the Formaloo field in front of the field’s name in parentheses.