Tag: code

Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 2                                         <<Intro>>

In our last tutorial, we grabbed the Google Sheets count data from a single rating survey question. We then displayed it two ways; horizontally with the question item at the top, or vertically with the question item to the side.

Google Sheete count data from rating survey

In part 3 of our Google Sheets data transformation with Google Apps Script course we are going to expand on our current work and add two more rating question items from our survey. Each survey will have the same 1. Weak to 5. Strong survey items.

Our new list of question items will include:

  • Rate your goat’s athleticism.
  • Rate your goat’s agility.
  • Rate you goat’s headbutt

All the information any self-respecting human companion for goats would know.

We’ll be updating our code to be able to take any number of item questions so long as they have the same choices.

Continue reading “Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets”

How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.

Google Sheets, Google Apps Script, onEdit

Sometimes, when you are working on a shared Google Sheet you might want to hide a row based on a cell value.

For example, perhaps we don’t want to see row information of orders that have been paid for. Take a look at my D&D miniatures wholesale orders sheet (image below). I know I don’t need to follow up the orders that have been paid, marked with a ‘Yes’ in column F, so I don’t want to see them on my sheet.

We want to hide the "Yes" - Google Sheets

We can hide these two ways:

  1. Create a filter.
  2. Google Apps Script onEdit() trigger.

Continue reading “How to Hide a Row based on a Cell Value in Google Sheets with Filter or Google Apps Script.”

Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.

Google Apps Script: SpreadsheetApp, copyTo

Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width just does not budge.

copied Google Sheet
When you want to copy and paste this…
Paste without column widths.
…but you get this.

Continue reading “Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.”

Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. 

Google Apps Script and Google Sheets

Imagine that you have a Google Sheet that you have sorted by a certain column. You might be sorting by the surname of your sales team, class sections or regions. To make the sheet easier to read for your team, you want to alternate the background colours after each category in your sort column is complete.

The Example

I have the following list of numbers in column 1 and 2. I have sorted these numbers by my Grouping Column of planets in column 3. After each grouping, I have alternated the background colour to make the transition easier to read.

Alternating color by section - Google Apps Script

The Code

Continue reading “Google Apps Script – How to Alternate Colors in an Ordered List by Column Category. “

How to make multiple copies of a file with Python 3 and a file name list from an Excel sheet

Python 3, openPyXl, os,  shutil on Windows 10

The Problem

As an academic administrator, I have to prepare 70 empty grade report spreadsheets templates at the end of each academic quarter: one for each of my teachers. Each copy of the template sheet needs to be named with the teacher’s name and class number. Then the quarter, title and year is appended to the end. For example:

Stephen Hawking 404-23 Q3 Grades 2017.xlsx

The hard way would be to copy and paste a file click the file and rename it, repeating the process 70 error-prone and mind wastingly dull times.  I could also get the teachers to rename the file, but…they are teachers, not administrators so…yeah…errors again.

Python 3 to the rescue:

Continue reading “How to make multiple copies of a file with Python 3 and a file name list from an Excel sheet”