How to use the Formulas

The formulas/bulk edit tool is very useful to make advanced editions on large amounts of rows.

If you need help writing a formula for the bulk edit, you can contact us and we will help you.

Please make a database backup before running any formula or advanced edit, because if you make a mistake you can mess your data.

Placeholders

We can use placeholders in the bulk edits or formulas for using dynamic information. We have the following placeholders available:

  • $current_value$ = the current value of the field
  • $random_number$ = a random number between 10000 and 999999
  • $random_letters$ = 6 random letters
  • $current_timestamp$ = current timestamp
  • $current_time_friendly$ = current time using the format set in WordPress general options.
  • $current_date$ = Current date in the format: d-m-Y
  • $random_date$ = Random date in format Y-m-d H:i:s starting from January 1st, 2 years ago until today. Available since CORE v2.14.1
  • $column_key$ = You can reference other columns in the formulas by wrapping the column key with $ signs. For example. $post_title$ , $post_content$ , $post_status$ , $my_custom_field$, etc. Since CORE v2.21.0, you can right click on any column in the cells and use the option “show column key” in the contextual menu to get each column key
  • $uuid$ = Generate a UUID. Available since CORE v2.15.4.1
  • $uniqid$ = Generate a unique id. Available since CORE v2.15.4.1
  • $current_value_lowercase$ = Current value as lowecase. Available since CORE v2.15.4.1
  • $current_value_uppercase$ = Current value as lowecase. Available since CORE v2.23.0.1
  • $current_value_capitalize_each_word$ = Current value with the first letter of every word as uppercase (i.e. turn “my car” into “My Car”. Available since CORE v2.15.4.1
  • $sequential_number$ = It will return a sequential number for every row processed in the bulk edit. For example, if you want to create titles like “Title 1, Title 2, Title 3”. You can bulk edit 3 rows and set value “Title $sequential_number$”.
  • $parent:COLUMN_KEY$ = This can be used in product variations or any page that has a parent page. This will return a value of any column of the parent. For example, you can use $parent:_sku$ to return the SKU of the parent product. You can get the key of the parent column by going to the sheet > right click on the column > show column key.

Custom formulas

This is used only when you select the type of edit “custom formula” in the bulk edit tool. We have 2 formulas: REPLACE and MATH. These formulas can be used for thousands of advanced edits.

The syntaxis is the following (quotes required):
=REPLACE(""Replace this"", ""with this"")
=MATH("4 + 3 – $current_value$")

Examples:

      • =REPLACE(""bad word"", ""good word"")
        Replace one word or phrase with another.
      • =REPLACE(""$current_value$"", ""$current_value<br/>Thank you for reading my post!"")
        Add a message at the end of the post. It can be plain text or html.
      • =REPLACE(""$current_value$"", ""Welcome to my site!<br/>$current_value$"")
        Add a message at the beginning of the post. It can be plain text or html.
      • =REPLACE(""<img"", ""Do you like the image below? Share it!<br/><img"")
        Add a message above the post images asking the user to share the image. It can be plain text or html.
      • =MATH("$current_value$ + 10")
        Increase the prices of the products by $ 10.
      • =MATH("$current_value$ * 1.1")
        Increase product prices by 10%.
      • =MATH("$current_value$ + ( 0.234343898 * 0.1) – 29 / 28 ")
        A complex math operation. You can use any math formula
      • =REPLACE(""$current_value$"", ""$_regular_price$"")
        Applying the formula to the sale price field, you can copy the value of the regular price into the sale price field.
      • =REPLACE(""$current_value$"", ""Posts published by $author$ on $date$"")
        Applying the formula to the “category” field, you can create categories combining other fields.
      • =REPLACE(""[::regex::]/^( +)(.+)( +)$/s"", ""$2"")
        Removes the spaces at the beginning and end of the values. For example, remove trailing spaces from the post content or prices
      • =REPLACE(""[::regex::]/(https\:\/\/youtu\.be\/.+)/"", ""[ embed width="123" height="456"]$1[/embed ]"")
        Wraps plain YouTube URLs with a shortcode
      • =REPLACE(""[::regex::]/(\A.*)/m"",""<h1>$1</h1>"")
        This will wrap the first line of text in any text column with h1 tags
      • =REPLACE(""[::regex::]/\r?\n?[^\r\n]*$/s"",""$1"")
        This will remove the last line of text in any text column
      • =REPLACE(""[::regex::]/<img[^\>]+\>/i"","""")
        This will remove all the tags from the content
      • =REPLACE(""[::regex::]/<p><span[^>]+>([^<]+)<\/span><\/p>/"", ""<p>$1</p>"")
        This will remove all the span tags from inside p tags
      • =REPLACE(""[::regex::]/<h2>([^<]+)\?<\/h2>/"", ""<h2>$1</h2>"")
        This will remove the final question mark from inside h2 tags. For example, this text “<h2>How to get to Amsterdam?</h2>” becomes “<h2>How to get to Amsterdam</h2>”.
      • =REPLACE(""[::regex::]/<img[^>]+>/"", """") This will remove all the images (<img> tags) from the current value.

Regular expressions

This is for advanced users. The replace formula accepts regular expressions (regex). You just need to prepend the “search” value with [::regex::] and enclose in forward slashes (/). Please check the example below:

Example:

=REPLACE(""[::regex::]/house(.+)/s"", ""Car $1"")

Please test your regular expressions on one row before executing them, you can use this online tool to test your regex syntax. If your regular expresion doesn’t match with the existing content, nothing will change.

Notes

  • The math formula must be used only on numeric columns. For example, product prices. If you use it on text fields, like post titles, the formula will stop executing and throw an error when it finds a non-numeric value.
  • The math formula works with numeric fields with computer format, not human format. For example, 100000.00 is allowed, but 1,000,000.89 is not allowed (the commas aren’t allowed, just decimals with a dot).
  • The replace formula accepts HTML but only safe/simple tags and attributes like links, paragraphs, images. For example, script tags will be removed.
  • If you use a text that contains html with the “style” attribute, the replace formula might remove the last semicolon from the css, making the search term not match.
  • If you use a text that contains html with attributes using a single quote, i.e. class=’test’, the replace formula might remove the attribute. Use double quotes to avoid this.
  • The “formulas” tool is very powerful and it must be used carefully. You MUST backup your database before executing formulas, if you enter the wrong formula you can mess up your data. Use it at your OWN RISK. We are not responsible for any data loss or disruption to your business caused by this plugin or mistakes you make when executing formulas.

PHP functions

This is for programmers or developers only and it can be used for many purposes.

Our bulk edit process will get the rows from the database, and it will call the php function for every field that will be edited. This way, your php function will receive the current value and the row ID (post ID, user ID, term ID, or ID of what you’re editing) and you will apply any edit that you want, and you will return the modified value so our bulk edit process can save it automatically.

Notes

  • We only accept php function names. For example, editMyTitle is a valid function name if you have defined that function
  • You can add the php code anywhere, it can be in the functions.php of your theme, in a plugin, using the code snippets plugin, etc.
  • We don’t allow object methods like $object->editMyTitle. We only accept regular functions. If you have the functionality inside a class, you can create a wrapper function and use the name of the wrapper function. Your wrapper function might look like this:

function editMyTitle($current_data, $post_id){
return $object->editMyTitle($current_data, $post_id);
}

  • You will do the edit of the value. We just call the function and pass the current value and ID, and you must do the rest.
  • If you want to call the php function and don’t edit any values, you can run the bulk edit on any column and your PHP function must return the existing value ($current_data without changes). If the php function returns the same value, the bulk edit will not save any changes.
  • IMPORTANT. Your function must return a scalar value ALWAYS because our bulk edit will save the returned value. So if your function returns null, we will clear the field and you would lose the data in that field. Accepted value types are string, integer, and boolean. For example, you run the php function on the title column using the posts spreadsheet, so we will call the function for each post and pass the existing title and save the value returned by your function as the new title.

Examples

1- You have 100 posts and each post is a movie, so you want to call the external IMDB API to get the rating of each movie and save it as post meta. You can define this php function in your theme’s functions.php file:

function wpseGetMovieRating($current_rating, $post_id){
$post = get_post($post_id);
// Make http request to IMDB's API using the post data and save the rating in the $rating variable that we will return
$rating = 10;
return $rating;
}

And you can run the bulk edit on the “Rating” column using the posts spreadsheet. So we will call wpseGetMovieRating for each post (movie) and save the rating number returned by the function. We will save it on the “rating” column where you ran the bulk edit.

bulk-edit-movie-rating-external2- You want to send an email to each user to notify them about a promotion, so you want to call the PHP function for each user but you don’t want to edit anything. You can define this PHP function in your theme’s functions.php file:

function wpseSendEmailToUser($current_data, $user_id){
$user = get_userdata($user_id);
wp_mail($user->user_email, 'My email subject', 'My email message here');
return $current_data;
}

You can run this bulk edit on any column of the users spreadsheet, the column doesn’t matter because we call the function and we just send the email and return the same existing value so our bulk edit won’t change anything. In this example, we run it on the email column.

bulk-edit-send-user-email

As you can see, the possibilities are endless. You can take advantage of our bulk edit process to execute any code on any column for super advanced edits.