Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

I recently had a legend (a.k.a reader) ask about how to copy selected columns from one sheet and append them to the bottom of different selected columns in another sheet. 

To me, this seemed like a pretty common task that one might face, so in response I decided to create a template function to easily do this and reuse on the same project or in other projects. 

But first, let’s take a look a the problem a bit more. 

We want:

  1. the freedom to select any number of columns from one sheet.
  2. to select the same number of different columns from the sheet we want to paste to.
  3. to paste to the bottom (append) the receiving sheet.
  4. to take into account varying header row heights.
  5. to have a “Completed” or “Copied” column in our origin sheet so that we don’t continue to paste all the data instead of simply what has been updated.
  6. have the data with formulas that we only want to paste the values for.

The Example

Let’s say we have a sheet that is updated regularly and we want to be able to paste data from columns A,B and C of this source sheet and append it to columns C,E and A of the destination Sheet. 

  • Copy Sheet 1 Col A -> Append Sheet 2 Col C
  • Copy Sheet 1 Col B -> Append Sheet 2 Col E
  • Copy Sheet 1 Col C -> Append Sheet 2 Col A

Take a look at our two Google Sheets 

SourceSheet - Google Sheets
DestinationSheet - Google Sheets

You can see in the Source Sheet (Sheet1) that we have 3 data columns and one column market To Copy. The To Copy column will be updated with “Copied” once the code is run successfully.

In the Destination Sheet (Sheet2) you can see that we already have some data there. We don’t want to overwrite it. We simply want to append our data to the bottom of it. 

Continue reading “Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another”

Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.

Google Apps Script, DriveApp

I had an unexpected need to change some non-native file names – in my case, MS Word docs – to something else to appease my masters. Being the resident Igor, I delighted in the task. 

The Problem

I had a folder of MS Word documents on my Google Drive that needed to all be renamed. They all needed the same name with an index number to distinguish that they were different files. 

There were, however other file types in the folder. 

The problem with non-native files in Google Drive is that it’s a little tricky to find their ID. Besides I just wanted to change ALL files that were MS Word in the folder. 

What I would need to do is search for all files inside my selected folder that are of MS Word type and rename them with the same name but with a counter at the end. For example:

Old File NameNew File Name
boring.doccranberrySauce.doc
sad.doc cranberrySauce1.doc
yawn.doc cranberrySauce2.doc
Continue reading “Google Apps Script – How to Change the File Name of Non-Native Files like MS Word Docs, PDF’s and Excel files.”

Instagram Diaries 3 – Day One and My First Pics

If  you missed Part 1 of the Instagram Diaries – how it all began – you can click here!

The cat’s out of the bag! Mrs Yagi got wind of what I was doing and read my first article on the Instagram Diaries.  So much for that secret-not secret rant in the last post. Ha!

Day One of posting was a challenging day. I had some choices to make. Do I start uploading photos of my most recent vacation or do I start at the very beginning of the photos from my phone all the way back in 2016?

Those first photos happened to be taken on a trip to Egypt and there were trips to Australia, Jordan, Sri Lanka, Thailand and Romania that I could add as well ( Oh Yagi! You place dropping hussy!). I like the idea of starting from the beginning for two reasons:

  1. I wouldn’t have to scramble for pictures now. Instead I could pick the best from what I had and slowly release them 1 to 3 a day.
  2. When else am I going to look at these photos? I reckon I am like almost everyone else and take photos for the sake of taking photos and never really go back on a trip down memory lane. 

First Pic Away

Awh yeah! Cool! I remember that little guesthouse with perfect views of the Pyramids, I thought. 

Continue reading “Instagram Diaries 3 – Day One and My First Pics”

Instagram Diaries 2 – Creating and Instagram Business Account, Getting the Perfect Username and Profile Image

If  you missed Part 1 of the Instagram Diaries – how it all began – you can click here!

Well, here we go.

One of the benefits of not being 20 year old me is that I am a little more cautious and less willing to trust my abilities in favor of listening to successful people and following their advice. 

Okay, I fess up. I am more likely to research and take others peoples advice more than I did twenty years ago. There!!! Is that okay? Jeez!!!

So the gurus of Instagram, those twenty-teen titans, were telling me that I should either upgrade my current Instagram account or create a new account.

Hmm… the though of updating my personal Instagram account and dicking around with all sorts of marketing tests did not seem fair to the few friends who bullied me into joining Instagram against my will in the first place.

Plus I got a bit of a confession to make…

Continue reading “Instagram Diaries 2 – Creating and Instagram Business Account, Getting the Perfect Username and Profile Image”

Instagram Diaries 1 – Getting Started

This might just be my midlife crisis! The start of my bumbling adventure on #Instagram

This might just be my midlife crisis!

A little over a month ago I turned 40. I could tell I was going to hit the big four-oh because of all the aches and pains that take longer and longer to deal with than they used to. 

On top of that, the new workload got hard. I mean real hard. I’m used to pulling 10+ hour days once or twice a week, but this year, it’s been every single day.

I know I needed a side project that was fresh and light. I needed a break from the coding and spreadsheets. Like any bloke in his midlife crisis, I needed to go out here and hit the latest young thing in a sad and desperate effort to rekindle my youth. 

That young thing, I thought, would be Instagram. Now, I know Instagram is no baby, but we need to to talk comparatively here. I’m on that slow rise to the “Old Fart” zone and five years older than 90% of the demographic

So to prove my youth one last time (maybe), I set out to learn what I could about Instagram. What I didn’t expect was the utter amount of information, and a lot of it data driven too! I was really starting to warm to the idea.

Continue reading “Instagram Diaries 1 – Getting Started”

Google Apps Script – How to Create Folders in Directories with DriveApp

Google Apps Script – DriveApp

Well, it’s  a rainy day here travelling in Romania, so time for a post.

coding in Romania
Relaxing in a cafe in Cluj Napoca, Romaina on a Rainy. I love this country!!!

When creating a Google Apps Script’s I often find I am creating new folders and files in specific locations on Google Drive after, say, generating a report or something.

A Note on Folders in Google Drive

All files and folders in Google Drive are allocated a unique key that identifies them.

Google Drive Folder ID

The file location and all the data about the file is mapped to this ID. This means you can have as many folders or files with the same name even in the same directory without a duplicate error being thrown because they all have their own unique ID for their URL.

Getting Started

More often than not, I know the parent folder that I want to put my sub folders in. This means I can get the parent folder’s ID and use that as my starting point to add sub folders. To do this we use the DriveApp class.

Below are three useful functions for creating folders.

  • Simple – Create a folder under the Parent folder ID – Duplicates are not checked and there can be multiple sub folders with the same name but all have their own  unique id.
  • Medium – Create a folder only if that folder name does not exists in the Parent folder – No folder is created if the folder already exists.
  • Hard-ish – Create a folder. If the name exists, add a counter to the name – If the file already exists then add a counter to the end of the file name.

All the functions will take two arguments: folderID – the unique id of the parent folder and folderName – the name you want to call your new folder.

The start() function will simply grab the two variables for the folderID and folderName and run the folder creation function. This is to simulate using the functions in your code.

Feel free to read what you need. I try and write these for a wide range of coding skill in mind.

Continue reading “Google Apps Script – How to Create Folders in Directories with DriveApp”

Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets

Google Apps Script, Jquery, Javascript, HTML

One of the weekly tasks in my day job as an academic administrator is to look at a number of ‘modules’ or courses that our college is running and complete some analysis and crosschecking. Each week the students complete a ‘unit’. However, during some quarters, not all modules are doing the same unit. Before I can run my code I need to determine what modules are running and what units we are up to for me to run my automated code.

To do this I created a dialog box when the code is run from the add-on bar. In a few clicks, I can then choose the relevant modules and units and then run the selected code.

Let’s take a look at what the dialog box looks like:

Dialog Form in Google Apps Script

Upon “Submit”, the dialog box returns an array  of objects of checked values from the radio buttons that can be uses in the server-side Google Apps Script.

In this tutorial we will look at the following :

Continue reading “Google Apps Script – Getting Input Data From a Dialog Box in Google Sheets”

Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time in Google Sheets.

Google Apps Script, Google Sheets

On a recent board post a Google Sheets user wanted to change a four digit number (for example, 1230) to a time, like 12:30, in the same cell that the item was entered.

Unfortunately the user was not in a position to change the starting values, so they were left with the 4 digits.

There are two ways of doing this with varying levels of complexity:

 

  1. The Google Sheets Formula Approach
  2. The Google Apps Script onEdit Approach

Continue reading “Google Apps Script – How to Automatically Generate a Time From a Four Digit 24 hour Time 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

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.”

Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing

What is Sections to Sheets?

Sections to Sheets is a free Google Sheets Add-on that separates rows by column sections and puts them in individual pages or sheets in a spreadsheet in preparation export and printing.

You can find Sections to Sheets at the Google Chrome Webstore.

Chrome Web Store Badge

Example 1

Let’s say you want to print out a list of expenditures by department and you want each department to start on a new page. You’ll also want to keep the same header for each page.

Example 2

You might want to export a list of grades by class number. You sort the grades by class and then export the list with each class starting on a new page with a header and footer.

Sections to Sheets can help you achieve this quickly by creating a new Google Spreadsheet and separating each selection and putting it into a new Sheet(tab) with or without headers and footer. This will enable you to quickly export or print your sheets.

Continue reading “Sections to Sheets – Free Google Add-on That Separates Column Sections Into Individual Sheets for Page-by-Page Export or Printing”