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: Get File By Name – With Optional Parent Folder Crosscheck

Google Apps Script

One of my recent projects in Google Apps Script required me to search for a file by name and get its ID. This can be problematic in Google Drive because you can have multiple files of the same name in multiple locations. My solution was to also check the file’s parent folder name as well. 

I created a function getFileByName() to handle this. The function takes the following parameters:

  • fileName – The name of the file you are looking for. (required)
  • fileInFolder – The parent folder of the file your are searching for. (optional)

The file path would look a little something like this:

.../fileInFolder/fileName

getFileByName() returns an object containing :

  1.  the ID of the file if the file exists or false if it does not.
  2. the ERROR if there is an error or false if there is not. 

The returned object would look like the following:

Successful

Error

getFileByName() reports the following errors:

  1. If no parent folder name is given and there are more than one file with the same file name. 
  2. There is more than one parent folder and file combination with the same name.
  3. There are multiple files with the same name but none are in the folder parent name provided.
  4. No file with the file name provided exists.

Continue reading “Google Apps Script: Get File By Name – With Optional Parent Folder Crosscheck”

Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget

Javascript, WordPress, Your Affiliate Program’s Campaign Link

The Story

When I created Yagisanatode.com my goal was to provide a resource for myself and others to reference on all the coding projects I work on. Since it’s beginnings in October 2017 I have seen a huge rise in my readership and am so please to see a growing community in my comments sections. 

Your support has really helped me to produce more and, I hope, produce better content. Thanks.

Just take me to the code!!!

Continue reading “Simple Code for AB Testing Affiliate Recommendations in a WordPress Sidebar HTML Widget”

Google Sheets – Remove The Lowest Grade for Each Student on a Course

Google Sheets – MIN, FILTER, INDEX, MATCH, SUM, COUNTIF

In the region of the world that I work in, it is a pretty common occurrence for university courses to run weekly assessment. At the end of the course all the weekly assessment is then added together minus the lowest piece of assessment.

For lecturers with small course sizes this is a pretty simple task that you could simply eyeball if you have a small enough group, but what if your course runs into the thousands with half a dozen tests to choose from. Eyeballing is just not going to do it.

Recently I was asked to do the same thing for the program that I manage. Over an 8 week term, we run 7 assessment at the end of each week for our students.  My job was to find the lowest grade out of the 7 assessment and drop it, taking note of the assessment unit that I dropped for each student. 

I use Google Sheets for this purpose for it’s ease of use and sharability.

This is an example dataset of the 7 assessment (in this case, weekly tests) in Google Sheets. We need to remove the lowest grade from each student. As you can see not all students have their lowest grade in the same Unit test. 

List of 7 grades for each student - Google Sheets
Continue reading “Google Sheets – Remove The Lowest Grade for Each Student on a Course”

Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER

This week I was asked to provide a list of the top two student grades in each of the 100 classes in our university program. 

Normally, this would be a pretty easy task if there were only a half dozen or so classes. Simply sort by group (class) and then Grade. Finally copy and paste the top 2 ranked students of each group in a new sheet – print and hand to the boss. 

However, with 100 classes this was going to turn into a time consuming chore and one that could be prone to errors. 

To solve this problem, I took advantage of Google Sheets RANK.EQ and FILTER formulas. Let’s take a peek at the formula now and go through the steps in detail later.

Let’s move on to an example.

Continue reading “Google Sheets – Get the Top 2 Grades for Each Group in a Spreadsheet using RANK and FILTER”

Google Apps Script – How to make a Custom Function to Use in Google Sheets

Google Apps Script

Google Sheets has a vast library of functions (or formulas) you can use to get your Spreadsheet tasks done. However, there are some instances when you want a specific function that is not available in Google Sheets or want to make a simplified version of some combined function to make life easier for your users. Fortunately, Google has your back with Google Apps Script.

Before we start, Google does a pretty great job explaining the basics of creating a custom function. However, when it comes to explaining how to add all that information that goes into a function when you type it into Sheets it is a bit vague.

Custom Function Decorators Google Apps Script

Let’s look at the creation process through a recent example I created:

Continue reading “Google Apps Script – How to make a Custom Function to Use in Google Sheets”