# Creating Functions

This guide explains how to create custom functions in SheetPoet to exchange data between WordPress and Google Sheets.

# Function Types

SheetPoet supports three types of functions:

Type	Purpose	Data Flow	Default Parameters	Behavior
Import to Sheet	Send WordPress data to Google Sheets	WordPress → Google Sheets	`index`, `processed`, `batchSize`	Runs recursively until cancelled or empty return
Upload to Website	Process data from Google Sheets	Google Sheets → WordPress	`index`, `batchSize`, `identifier`	Supports dynamic column values from sheet
One-time Trigger	Execute standalone operations	No specific data flow	None	Runs once and inserts data into selected sheet

# Function Behavior Details

Import to Sheet: When this operation is run from the sheet, it will run recursively until you cancel the running operation or return an empty value in the function. Once either condition is met, the data import will stop.

One-time Trigger: This will be run just once when its operation is executed from the sheet and inserts the data into the selected sheet.

Upload to Website: This supports dynamic column values from the sheet. For example, if you want to get column B value in a key called test, set a magic parameter with key test and value {{B}} so you can use the value in your function to insert it in the database, post meta, or do anything you want with it.

# Creating a New Function

Go to SheetPoet > Functions
Click Add New Function
Choose a function type
Enter a display label (shown in Google Sheets)
Write your PHP code
Click Validate Function to check for errors
Click Save Function

# Function Requirements

All functions must:

Accept a $record parameter (all three function types use this parameter)
Return a value: string, array, or array of objects
Use valid PHP syntax
Not use restricted functions (like eval, exec)

# Default Parameters Available

Each function type automatically receives these parameters in the $record array:

Import to Sheet: index, processed, batchSize
Upload to Website: index, batchSize, identifier
One-time Trigger: No default parameters (only custom magic parameters)

# Example Functions

# Import to Sheet Example

Retrieves posts in batches and sends them to Google Sheets. This function runs recursively until all posts are imported or you stop the operation:

function sp_get_recent_posts($record) {
    // Default parameters: index, processed, batchSize
    $index = intval($record['index']);
    $batch_size = intval($record['batchSize']);
    $processed = intval($record['processed']);
    $offset = $index * $batch_size;

    $args = [
        'post_type'      => 'post',
        'posts_per_page' => $batch_size,
        'offset'         => $offset,
        'post_status'    => 'publish',
    ];

    $query = new WP_Query($args);
    $posts = [];

    if ($query->have_posts()) {
        foreach ($query->posts as $post) {
            $posts[] = [
                'ID'       => $post->ID,
                'Title'    => get_the_title($post),
                'Content'  => wp_trim_words($post->post_content, 20),
                'Date'     => $post->post_date,
                'Author'   => get_the_author_meta('display_name', $post->post_author),
                'Status'   => $post->post_status,
                'Link'     => get_permalink($post),
            ];
        }
    }

    // Return empty array to stop recursive processing when no more posts
    return $posts;
}

# Upload to Website Example

Creates posts from spreadsheet data using dynamic column values. Set magic parameters like for example:

key	value
title	{{A}}
content	{{B}}
category	{{C}}
status	{{D}}
param1	Example static val

then, you can access them in your function like this:

function sp_create_posts($record) {
    // Default parameters: index, batchSize, identifier
    $index = intval($record['index']);
    $batch_size = intval($record['batchSize']);
    $identifier = $record['identifier'];

    // Dynamic column values from magic parameters
    $title = isset($record['title']) ? $record['title'] : '';
    $content = isset($record['content']) ? $record['content'] : '';
    $category = isset($record['category']) ? $record['category'] : '';
    $status = isset($record['status']) ? $record['status'] : 'draft';

    // Validate required fields
    if (empty($title)) {
        return [
            'Status' => 'Error',
            'Message' => 'Title is required'
        ];
    }

    // Create the post
    $post_data = [
        'post_title'   => sanitize_text_field($title),
        'post_content' => wp_kses_post($content),
        'post_status'  => sanitize_text_field($status),
        'post_type'    => 'post'
    ];

    $post_id = wp_insert_post($post_data);

    // Add category if provided
    if (!empty($category) && !is_wp_error($post_id)) {
        wp_set_post_categories($post_id, [get_cat_ID($category)]);
    }

    return [
        'Post_ID' => is_wp_error($post_id) ? 0 : $post_id,
        'Status' => is_wp_error($post_id) ? 'Error' : 'Success',
        'Message' => is_wp_error($post_id) ? $post_id->get_error_message() : 'Post created successfully'
    ];
}

# One-time Trigger Example

Performs a maintenance task and returns results to the sheet. This runs once and inserts data into the selected sheet:

function sp_cleanup_transients($record) {
    global $wpdb;

    // No default parameters for One-time Trigger
    // Only custom magic parameters are available
    $cleanup_type = isset($record['cleanup_type']) ? $record['cleanup_type'] : 'transients';

    $results = [];

    if ($cleanup_type === 'transients') {
        // Delete expired transients
        $deleted = $wpdb->query(
            "DELETE FROM $wpdb->options
             WHERE option_name LIKE '%_transient_%'
             AND option_value < " . time()
        );

        $results[] = [
            'Task' => 'Cleanup Transients',
            'Items_Deleted' => $deleted,
            'Status' => 'Completed',
            'Timestamp' => current_time('mysql')
        ];
    }

    if ($cleanup_type === 'revisions') {
        // Delete post revisions
        $deleted_revisions = $wpdb->query(
            "DELETE FROM $wpdb->posts WHERE post_type = 'revision'"
        );

        $results[] = [
            'Task' => 'Cleanup Revisions',
            'Items_Deleted' => $deleted_revisions,
            'Status' => 'Completed',
            'Timestamp' => current_time('mysql')
        ];
    }

    return $results;
}

# Best Practices

Validate input data to prevent errors
Include error handling for robust functions
Use descriptive names for clarity
Keep functions focused on a single task
Test with small datasets before processing large amounts

# Next Steps

After creating functions:

For troubleshooting help, see our Troubleshooting Guide.