How can I dynamically populate a Google doc template?

Anvil Q&A
1

I have an application that creates a list of 3-5 “jobs” that the user details. For each job there are one or many “Interviewees” that may be associated with the Job. This is all working fine on the client side.

I have a template Google Doc file setup that has placeholders for all the key data. There is a single template for Job details and one for Interviewees in the google doc.

I am trying to create a function that will:

  1. Copy the template google doc for the right number of jobs, and the right number of interviewees per job
  2. replace the placeholder text to capture the data from the server

I can’t seem to work out how to get the template to copy and populate the data properly:

Code Sample:

def replace_placeholders(doc_id, placeholders):
    doc = anvil.google.drive.get(doc_id)
    requests = []
    for placeholder, value in placeholders.items():
        requests.append({
            'replaceAllText': {
                'containsText': {
                    'text': f'<<{placeholder}>>',
                    'matchCase': True
                },
                'replaceText': value
            }
        })
    doc.batch_update(requests=requests)

def format_list_as_bullets(items):
    return "\n".join([f"- {item}" for item in items])

@anvil.server.callable
def export_to_google_docs(project, jobs_data):
    # Load the Google Docs template
    template_file = app_files.job_template

    # Create a new document using the template content
    new_doc_content = template_file.get_bytes()
    new_doc = anvil.google.drive.create_file(f"Job Details for {project['project_name']}.docx", content=new_doc_content, mime_type="application/vnd.openxmlformats-officedocument.wordprocessingml.document")
    new_doc_id = new_doc['id']

    for job in jobs_data.values():
        job_placeholders = {
            'job_title': job['jtbd_name'],
            'global_push': format_list_as_bullets(job['global_push'].split("- ")),
            'global_pull': format_list_as_bullets(job['global_pull'].split("- ")),
            'more_about': format_list_as_bullets(job['more_about'].split("- ")),
            'less_about': format_list_as_bullets(job['less_about'].split("- ")),
            'hiring_cues': format_list_as_bullets(job['hiring_cues'].split("- ")),
            'firing_cues': format_list_as_bullets(job['firing_cues'].split("- ")),
            'functional_motivation': job['functional_motivation'],
            'emotional_motivation': job['emotional_motivation'],
            'social_motivation': job['social_motivation'],
            'trade_offs': format_list_as_bullets(job['trade_offs'].split("- ")),
            'quotes': format_list_as_bullets(job['quotes'].split("- ")),
            'competitors': format_list_as_bullets(job['competitors'].split("- "))
        }

        # Generate interviewee content
        interviewees_content = ""
        for interviewee_name, interviewee_data in job['interviewees_dict'].items():
            interviewee_name_display = f"{interviewee_name} #{interviewee_data['story_number']}" if interviewee_data['story_number'] > 1 else interviewee_name
            interviewee_placeholders = {
                'interviewee_name': interviewee_name_display,
                'switching_from': interviewee_data['additional_data']['switching_from'],
                'switching_to': interviewee_data['additional_data']['switching_to'],
                'setup': interviewee_data['additional_data']['setup'],
                'pushes': format_list_as_bullets([push['Push'] for push in interviewee_data['additional_data']['pushes']]),
                'pulls': format_list_as_bullets([pull['Pull'] for pull in interviewee_data['additional_data']['pulls']]),
                'habits': format_list_as_bullets([habit['Habit'] for habit in interviewee_data['additional_data']['habits']]),
                'anxieties': format_list_as_bullets([anxiety['Anxiety'] for anxiety in interviewee_data['additional_data']['anxieties']]),
                'observations': interviewee_data['additional_data']['observations']
            }

            # Create interviewee content
            interviewee_content = (
                "<<interviewee_name>>\n"
                "Switching From: <<switching_from>>\n"
                "Switching To: <<switching_to>>\n"
                "Setup: <<setup>>\n"
                "When I am...\nSo I can...\n<<pushes>>\n<<pulls>>\n"
                "Habits...\nAnxieties...\n<<habits>>\n<<anxieties>>\n"
                "Observations\n<<observations>>\n"
            )
            interviewees_content += replace_placeholders_in_text(interviewee_content, interviewee_placeholders) + "\n"

        job_placeholders['interviewees'] = interviewees_content

        # Replace job-specific placeholders
        replace_placeholders(new_doc_id, job_placeholders)

    return f'https://docs.google.com/document/d/{new_doc_id}/edit'

def replace_placeholders_in_text(text, placeholders):
    for placeholder, value in placeholders.items():
        text = text.replace(f'<<{placeholder}>>', value)
    return text ```

What am I doing wrong?