1 – Open the spreadsheet in Google Sheets, and save is as HTML (File > Download > Web Page (.html)
2 – Unzip the downloaded file and open the included .html file (it also may include other files).
2 – Execute the following code in your browser console. It will load jQuery:
var script = document.createElement('script');
script.src = 'https://code.jquery.com/jquery-3.6.0.min.js'; // You can change the version if needed
script.type = 'text/javascript';
script.onload = function() {
// jQuery has been loaded and is available in the browser console.
console.log('jQuery has been loaded:', window.jQuery);
};
document.head.appendChild(script);
3 – Once that jQuery is loaded (it may take a second until your browser shows you a confirmation message) execute the following code too:
( function( $ ) {
$( 'span' ).each( function() {
console.log( $( this ).css( 'font-style' ) );
if ( 'italic' == $( this ).css( 'font-style' ) ) {
$( this ).prepend( '<em>' );
$( this ).append( '</em>' );
}
} );
} )( jQuery );
(Note: The code could be improved to include bold, underlined, etc.).
4 – Copy the full table into a new spreadsheet (be sure to select the full table.. on Firefox for MAC you can use CMD + A). You will need to remove some extra columns and rows that were created
(For example, the column letters, the row numbers and maybe some empty lines before the real content, the goal is to have the new formatted information into the right columns matching the original spreadsheet).
5 – Finally, you may want to apply the following tips for the import (that we used on the site when we created this tutorial):
- Keep column headings as simple as possible to prevet issues on future updates. For example… “title_case” it’s better than “Title Case ** in accessiblity terms ALL CAPS reads as acronyms so we need to change the case of all caps non-acronyms – Please review”
- If the column that you will use for the title includes <em>.. you may want to copy that column into a new column without <em> to prevent affecting the order of posts when sorting by title. (You can do this by copying the full column into your code editor, and find and replace <em> en </em> to empty strings, then, copy that text into the new column).
