When you start a project without a clear end in sight, it’s common to build it using various easy-to-use online tools. If you’re fortunate and the project materializes, you’ll likely feel the need to consolidate everything into a single application. This allows all the information you’ve gathered and processed to interact symbiotically.
This article details one such experience, where we chose Odoo as our company’s ERP after initially using Google Forms to gather customer information. Migrating the data itself was a straightforward task, as Google Forms allows exporting responses as CSV files. However, we needed to create surveys in Odoo to import these answers and continue collecting customer information in the new system.
We had around 20 surveys, some with dozens of questions and a few with hundreds. While it wasn’t impossible to transfer this manually — after all, we had manually created the Google Forms initially — it would have been an incredibly tedious task.
We did not find any Odoo module to do it, nor hints at stackoverflow to do anything similar, so we asked chatGPT and addressed the issue with little effort. This was the result we got (slightly changed to make it work):
Let’s break down this task into detailed steps:
Access the Google Form:
Use Google Apps Script to access an existing Google Form.
Retrieve the form’s questions, question types, and possible answers.
Generate XML Content:
Create a survey_data.xml file with the structure required by Odoo to import data from modules
The XML should include records for the survey, questions, and possible answers.
Output the XML:
Format the retrieved data into the XML structure.
Write the XML content to a file.
Pseudocode
Here’s a step-by-step plan in pseudocode:
Setup Google Apps Script:
Open the existing Google Form.
Retrieve all questions and their details.
Generate XML Content:
Initialize XML structure.
Add survey record.
Loop through each question:
Add question record.
If the question is of type “Multiple Choice” or “Simple Choice”:
Loop through each possible answer:
Add answer record.
Output the XML:
Convert the XML structure to a string.
Write the string to a file.
Google Apps Script
Here’s the full Google Apps Script code
const formId = ‘YOUR_FORM_ID‘; // Replace with your Google Form ID
const form = FormApp.openById(formId);
const items = form.getItems();
let xmlContent = ‘<?xml version=”1.0″?>n<odoo>n‘;
// Add survey record
const surveyId = ‘survey_survey‘
xmlContent += ‘ <record id=”‘ + surveyId + ‘” model=”survey.survey”>n‘;
xmlContent += ‘ <field name=”title”>‘ + form.getTitle() + ‘</field>n‘;
xmlContent += ‘ <field name=”description”>‘ + form.getDescription() + ‘</field>n‘;
xmlContent += ‘ </record>n‘;
items.forEach((item, index) => {
const questionId = ‘survey_question_‘ + (index + 1);
const type = item.getType()
xmlContent += ‘ <record id=”‘ + questionId + ‘” model=”survey.question”>n‘;
xmlContent += ‘ <field name=”survey_id” ref=”‘ + surveyId + ‘“/>n‘;
xmlContent += ‘ <field name=”question_type”>‘ + mapItemType(type) + ‘</field>n‘;
xmlContent += ‘ <field name=”title”>‘ + item.getTitle() + ‘</field>n‘;
xmlContent += ‘ </record>n‘;
switch (type) {
case FormApp.ItemType.MULTIPLE_CHOICE:
item.asMultipleChoiceItem().getChoices().forEach((choice, choiceIndex) => {
xmlContent += ‘ <record id=”survey_label_‘ + questionId + ‘_‘ + (choiceIndex + 1) + ‘” model=”survey.question.answer”>n‘;
xmlContent += ‘ <field name=”question_id” ref=”‘ + questionId + ‘“/>n‘;
xmlContent += ‘ <field name=”value”>‘ + choice.getValue() + ‘</field>n‘;
xmlContent += ‘ </record>n‘;
});
break;
case FormApp.ItemType.CHECKBOX:
item.asCheckboxItem().getChoices().forEach((choice, choiceIndex) => {
xmlContent += ‘ <record id=”survey_label_‘ + questionId + ‘_‘ + (choiceIndex + 1) + ‘” model=”survey.question.answer”>n‘;
xmlContent += ‘ <field name=”question_id” ref=”‘ + questionId + ‘“/>n‘;
xmlContent += ‘ <field name=”value”>‘ + choice.getValue() + ‘</field>n‘;
xmlContent += ‘ </record>n‘;
});
break;
case FormApp.ItemType.LIST:
item.asListItem.forEach((choice, choiceIndex) => {
xmlContent += ‘ <record id=”survey_label_‘ + questionId + ‘_‘ + (choiceIndex + 1) + ‘” model=”survey.question.answer”>n‘;
xmlContent += ‘ <field name=”question_id” ref=”‘ + questionId + ‘“/>n‘;
xmlContent += ‘ <field name=”value”>‘ + choice.getValue() + ‘</field>n‘;
xmlContent += ‘ </record>n‘;
});
break;
}
});
xmlContent += ‘</odoo>‘;
const file = DriveApp.createFile(‘survey_data.xml‘, xmlContent);
Logger.log(‘File created with ID: ‘ + file.getId());
}
function mapItemType( type ){
switch (type) {
case FormApp.ItemType.CHECKBOX:
case FormApp.ItemType.LIST:
return ‘multiple_choice‘
case FormApp.ItemType.MULTIPLE_CHOICE:
return ‘simple_choice‘
case FormApp.ItemType.SCALE:
return ‘numeric‘
case FormApp.ItemType.TEXT:
return ‘char_box‘;
case FormApp.ItemType.PARAGRAPH_TEXT:
return ‘text_box‘;
// Add more cases if needed
default:
return ” + type;
}
Steps to Execute
Open Google Apps Script.
Create a new project.
Copy and paste the script above.
Replace YOUR_FORM_ID with your Google Form ID.
Run the script.
Next Steps
a. Create an Odoo module and copy the generated survey_data.xml in the data folder.
b. Add the file location to the module’s manifest.py data array
c. Install the module in your Odoo database
Conclusion
Obviously it is not a complete solution, we added a loop to iterate over all the FORM_IDs that we had, there were some more question types to take into account, sections… but it is amazing how in a few minutes you can have a quite complicated problem addressed.
