Web Development

Google Forms to Google Docs with Apps Script

Google Apps Script

This past week I have had the opportunity to learn a new JavaScript-based language, Google Apps Script thanks to a tutorial provided by one of my clients.

It’s basically a scripting language used to integrate Google Apps with each other, such as Google Docs and Google Forms, Google Spreadsheets and Gmail, and so on.

One of my client’s requests was to create a PDF out of the form data once a Google Form is submitted. However, in order to do that, I need to create a separate template file with Docs, which is going to take very long considering it’s 7-page long when the form is printed out. And unfortunately, Google doesn’t support converting from Forms into Docs.

I won’t be copying the fields from a Google Form to a Google Doc one by one manually. Instead, I will be using Google Apps Script to loop through the field elements and copy them into a new Doc with a simple and readable format.

Here is the code I wrote:

/**
 * Adds a custom menu to the active form, containing a single menu item for
 * invoking checkResponses() specified below.
 */
function onOpen() {
  FormApp.getUi()
      .createMenu('Custom Actions')
      .addItem('Convert to PDF', 'formtopdf')
      .addToUi();
}

function formtopdf() {
  
  var form = FormApp.getActiveForm();
  
  // Create the required folder if it doesn't exist
  var rootFolder = '';
  var folderName = "Folder where the document will be generated in";
  if (DriveApp.getFoldersByName(folderName).hasNext() == false) {
    rootFolder = DriveApp.createFolder(folderName);
  } else {
    var matchedFolder = DriveApp.getFoldersByName(folderName);
    while (matchedFolder.hasNext()) {
      rootFolder = matchedFolder.next();
      break; // Only assign the first matched folder to rootFolder
    }
   }
  
  var contents = '\n';  // Initiate the contents variable with a new line
  
    // Get the title and description of the form and output to the document
  var formTitle = form.getTitle() + "\n";
  var subtitle = form.getDescription() + "\n";  
  contents += formTitle + subtitle + "\n";
  
    // Get all the responses that are submitted through the form
  var responses = form.getResponses();

  for (var j = 0; j < responses.length; j++) {
    var response = responses[j].getItemResponses();  // Get field names and corresponding values for the submitted data
    
    var doc = DocumentApp.create(formTitle + " for " + response[0].getResponse()); // Generate a document at the root of your account named title of the form plus the first value
    var docID = doc.getId();
    var docBody = doc.getBody();
        
    docBody.appendParagraph(contents);  // Add title and subtitle of the form to the document

    for (var item = 0; item < response.length; item++) {

      var title = response[item].getItem().getTitle(); // Get the field name
      var getResponse = response[item].getResponse();  // Get the submitted value
      
      docBody.appendParagraph(title + "\n").editAsText().setBold(false);  // Unbold the field text because of the loop
      docBody.appendParagraph(getResponse + "\n").editAsText().setBold(true);  // Bold the response text
      
      if (item == response.length - 1) continue;  // Prevent the output of horizontal line when it is the last item

      doc.appendHorizontalRule();  // Add a horizontal separator between items
     
    }

    doc.saveAndClose();  // Save the changes to the document and close it
        
  }

  // Notice to user that the generated PDF(s) are in the root of his Drive folder
  FormApp.getUi().alert(responses.length + " document(s) have been generated. They are available in your main Drive Folder.");
        
}

The above code should be self-explanatory since it’s well commented. If you still have some questions regarding the code, please leave a discussion in the comments below.

It basically loops through all of the form fields & values and outputs to the generated Doc. In the process, it makes values bold and places a horizontal line after each value to make the generated document more readable.

If you want to learn more about Google Apps Script, there are several ways. One is look through the sample code in the new project for each Google App. Another is to read and learn through the tutorials provided by Google. And another way is to just use the editor. The project editor has a nice auto-complete feature, and not to mention that the language itself has easy to understand syntax.

And of course, I will write more about the Apps Script while I’m learning it.

A little Nested foreach Tip

PHP

Here is a tip I found when fixing the database error for one of my clients.

<?php
	$row_parts = array("Monthly", "Special", "Transport", 10, 20);
	foreach ($row_parts as $row_part):
		foreach ($row_parts as $row_part_2):
			if ($row_part == $row_part_2):
				continue;
			elseif (!is_numeric($row_part) && !is_numeric($row_part_2)):
				continue;
			elseif (is_numeric($row_part) && is_numeric($row_part_2)):
				continue;
			endif;
			echo $row_part . "_" . $row_part_2 . "<br />";
		endforeach;
	endforeach;
?>

The above code outputs the following:

Monthly_10
Monthly_20
Special_10
Special_20
Transport_10
Transport_20
10_Monthly
10_Special
10_Transport
20_Monthly
20_Special
20_Transport

It uses a single array to store both words and numbers. Then combine them together through a nested foreach loop with an underscore to form reversible variables, reversible columns in this case.

I then can use these generated values to match the existing columns in the MySQL table and assign the corresponding variables to these values. For example, “20_Monthly” column would assign to $monthly_20 if “Monthly_20″ column is undefined.

It saves client’s, other developers’, and my time to look for the source of errors in the future if the database column change from “20_Monthly” to “Monthly_20″, assuming that the format of the database column names remain the same.

This type of error for changing the name of database column is often occurred during the server migration. An unexperienced client would manually type the database entries by himself or by hiring an assistant, instead of using export and import features in phpMyAdmin.

Have you had similar clients? And how did you respond to them? Discuss them in the comments section below.

A Nice way to use the foreach Loop

PHP

I have been using this technique for a long time. Because I wanted to decrease the code I want to write, I developed this technique. Another reason that I found this technique helpful is that I don’t have to remember the whole snippets when learning a new trick.

Here is an example of the technique for the foreach loop I’m talking about:

$theme_mods = array(
	"footer_bg_color" => "#ADFF2F"
);
foreach ($theme_mods as $theme_mod => $default):
	$$theme_mod = get_theme_mod($theme_mod, $default);
	if (empty($$theme_mod)):
		$$theme_mod = $default;
	endif;
endforeach;

For those who are wondering what the above code is about. It’s used to apply the Theme Customizer settings to a WordPress theme. Here is the link to get_theme_mod() function documentation page, which is what the above code mainly about.

As the code shows, I use nested variables to assign each value in the loop to different variables for use outside the loop. For instance, the key “footer_bg_color” in the array would be converted into $footer_bg_color to provide value “#ADFF2F”.

I often use this technique on forms, where the fields need to be checked whether or not it’s empty. If so, assign an empty value. Otherwise, retrieve the value from the database and store it to the variable.

Here is the usage for the above code:

<style type='text/css'>
.site-footer {
	background-color: <?php echo $footer_bg_color; ?>;
}
</style>

In the future, when I need to add additional items to the theme_mods list, I just have to insert needed keys and values to the theme_mods array and add the key variables to the corresponding code.

I think that’s what others called DRY (Don’t Repeat Yourself) practice? I use it as a method for laziness to remember stuff :)

Do you practice DRY method often or rarely? And can you remember new techniques through using this method as well? Please share them in the comment. Thanks.

Don’t Generate Stylesheet Dynamically

WordPress Tips

During the early stages of my theme development process, I planned to use a PHP-generated stylesheet as the main stylesheet. Borrowing the WordPress native functions in wp-load.php, I would be able to use WP functions inside the PHP file which is the source file that the stylesheet would be generated.

For those unfamiliar with the above meaning, here is a line of code which needs to be placed in the first line of the stylesheet to be “dynamically generated”:

<?php header("Content-type:text/css"); ?>

And add the php extension to the filename of the stylesheet (ex style.css.php), as well as change the corresponding code in functions.php in your theme folder from:

wp_enqueue_style("shadow_leaves-style-custom", get_template_directory_uri() . "/css/custom.css", array("shadow_leaves-style"));

to:

wp_enqueue_style("shadow_leaves-style-custom", get_template_directory_uri() . "/css/custom.css.php", array("shadow_leaves-style"));

However, during my research of how to dynamically include wp-load.php instead of hard coding it, I discovered this article which talks about the bad practice of loading wp-load.php into external scripts, and generated CSS/JS in general.

Basically, for wp-load, it would allow the server to load two instances of WordPress, cause high resources usage on the server, in turn causing slow down of the site.

As for the dynamic generated scripts, it’s a bad practice because the server needs to generate the stylesheets / scripts every time one of the pages on the site is visited, as well as caching.

Overall, the primary reason for not using wp-load and dynamically generated files is to prevent slow down of the site.

Did you use PHP header() function to dynamically generate either stylesheets or javascript files or both before? And what’s your creative use of the header() function? Please discuss in the comment below. Thanks.

One Small WordPress Theme Customizer Tip

WordPress Tips

I mentioned earlier that I’m working on my first WordPress theme.

In the process of learning more about the theme creating process, I stumbled across this article on WPMUDEV about the basics of WP Theme Customizer.

When I was following the tutorial on this page, all went well until the JS part.

Because I’m using Underscores as my starter theme, most of the basic Theme Customizer functions were already built-in, as well as some of the pre-made JS “templates”. All I have to do is copy the existing JS code into a new section and modify a little in order to follow along with the tutorial.

When I tested the new JavaScript code in the browser, the code I wrote didn’t load onto the page. After reading through the built-in code, here is what I modified in order to correctly load the recent version of the code (in the inc/customizer.php within the theme directory):

wp_enqueue_script( 'shadow_leaves_customizer', get_template_directory_uri() . '/js/customizer.js', array( 'customize-preview' ), '20130508', true );

Just search for “customize-preview” in your code editor to find the above line and either remove the “20130508” in this line, change it to a bigger number (such as 30130508), or change it to the current date (which is 20141202 as of this writing).

If you modify the JavaScript file multiple times in a day and don’t want to remove this version number in order to keep the caching feature on, you can add current time at the end of the number. For example, 201412020602, stands for December 02, 2014 06:02 AM.

It’s great that WordPress has this handy caching feature and it’s a great for the production server. However, it’s recommended that you also keep this versioning practice during local development instead of removing the version number in case you forgot to enable it when deploying to the server.

And if you read the above line of script carefully, you will notice that the name of the theme I’m currently developing will be called “Shadow Leaves”.

Wordpress Caching

Real User Feedback on my Mobile Site

Peek UserTesting

I recently discovered a new service called Peek which allows us, as developers (or at least a web site owner), to conduct a free real user feedback for our sites, in video format.

At first, I was skeptical at the service. Since it’s free, it wouldn’t cost me anything to sign up. So I used a temporary email address to sign up the service for span prevention reason. Typed my blog URL, blog.robbychen.com, and waited around 12 hours for the result to be received in the email inbox.

Here is the video I received.

Through watching this video, I discovered an important fact: the user feedback is very important for the solo developer like me, although I later noticed that the user recorded this video was paid by UserTesting, same company which made this useful tool. Still, it gives us three free tests per month to see the reaction of a real user.

I’d like to be a user tester just to experience what a user tester look like. However, thanks to my speech disability that almost no one can understand my speech, I have to pass this opportunity.

By watching through the entire video above, I noted the following areas that need to be modified to make better user interface:

  • Change the font family for the title and subtitle to be more readable
  • Get rid of the fixed navigation bar
  • Make sure to test the source code output on mobile to be smaller
  • Fix the layout problem for the tags in each post
  • Decrease the font size for the title and subtitle on mobile

Thanks to this free service, I was able to find out which area users frustrated most and fixed above issues.

Better Design

WordPress make_clickable() usage tips (Updated)

WordPress Tips

Update: I updated “echo” in the code below with “return” because it seems that the add_filter function will output duplicated content with the echo statement.

make_clickable() function is used to convert a text URL in the provided string into a link (make it clickable). It’s one of the useful functions in WordPress.

However, it can ruin your site if you use other features of WordPress, such as shortcodes. As it will convert any string that contains URL to a clickable link, including the URL in shortcodes, make the shortcodes invalid.

In order to ignore the URL in shortcodes, you need to generate the code from the shortcodes before converting any URL. Here is the code for it:

function mc_ignore_shortcode($content) {
	$content = do_shortcode($content);
	return make_clickable($content);
}

add_filter( 'the_content', 'mc_ignore_shortcode' );

It’s also missing a feature to make the external websites open in a new tab/window. I don’t know why, but I assume that all the text URL is from external websites (meaning outside of your website), here is the code for making all the links converted by this function open in a new tab/window (original code from this gist):

function blank_link($content) {
	return preg_replace('/<a /','<a target="_blank" ', make_clickable($content));
}

add_filter( 'the_content', 'blank_link' );

make_clickable() is an useful WordPress function. These two tips should help you to make make_clickable() function even more useful.

Prevent Tab Closing with an Userscript

userscripts

While it’s impossible to prevent close of tab directly using JavaScript, we can at least remind ourselves do not close the current open tab when pressing the CMD + W close tab keyboard shortcut.

Add the following script to either Greasemonkey for Firefox or Tampermonkey for Chrome on the site you want the reminder to appear:

// ==UserScript==
// @name       Always Google Calendar
// @namespace  http://blog.robbychen.com
// @version    0.1
// @description  Prevent close the Google Calendar window through keyboard shortcut
// @match      https://www.google.com/calendar/render
// @copyright  2014, Robby Chen
// @require    http://code.jquery.com/jquery-latest.min.js
// ==/UserScript==

$(document).ready(function() {
    $(document).on("keydown", function(e) {
        if (e.keyCode == "87" || e.keyCode == "93") {
            alert("Do not close this window. If you want to refresh this window, press Left command key and R.");
        }
    });
});

For the above code, change the meta information between “// ==UserScript==” as appropriate. Here is the documentation of the @ headers for Tampermonkey. It should be the same as Greasemonkey.

You can add the following code to the “keydown” event to display the keycode in the JavaScript Console for the specific key you would like to use instead of the W (87) and right CMD (93) keys in the code:

console.log(e.keyCode);

You can also change the reminder message to be displayed when the specified key is pressed by modifying the alert message. If you don’t like alert box, you can display the alert message to any exist element on the page. However, I find that the alert box is most useful reminder method.

Exclude stylesheet in Frontend for admin in WordPress

WordPress Tips

While is_admin() function works great within the backend, using this function in frontend will always return false. For example, consider the following sample code:

if (is_admin()):
	echo "is admin";
else:
	echo "not admin";
endif;

It will display “not admin” in the frontend (such as your homepage). When in the backend (such as WordPress admin) and whether you logged in as an administrator or not, it will display “is admin”.

In order to change layout for certain elements in frontend for non-administrators, you need to use current_user_can() function:

function custom_scripts() {
	if (!current_user_can("administrator")):
		wp_enqueue_style("noAdmin_style", plugins_url("notAdmin.css", __FILE__));
	endif;
}

add_action("wp_enqueue_scripts", "custom_scripts");

Note that if you placed current_user_can() if statement outside of a function and without any hook, it will display a fatal error which is discussed in this article.

Feel free to leave a comment below regarding the use of current_user_can() function.