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

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

Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions

Google Sheets, Google Apps Script

I was working on a Spreadsheet in Google Sheets a few days ago and needed to generate some random codes for my users. To do this, I just used the RANDBETWEEN(start val, end val) function built into Google Sheets.

It goes a little like this. If I want to build a 5 digit random number I would:

RANDBETWEEN Google Sheets

But this got me wondering. Is there a function for random Alphabetic strings or Alphanumeric strings or Alphanumeric with Character strings. The answer?

via GIPHY

 

No…

So instead I decided to make a custom function with Google Apps Script to do this job for me…

Continue reading “Google Sheets – Random Alphabetic, Random Alphanumeric and Random Alphanumeric + Character Custom Functions”

Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names

Google Apps Script

sighisoara Google Apps Script Tutorial
This tutorial comes from a comfy spot in the center of Sighisoara.

Well, that title is such a mouthful. So I think it needs some explaining…

I’ve recently come across a tasks that requires me to access a particular folder in a directory based on the information in a google Sheet. This means:

  1. I don’t have the folder or the sub-folder ID.
  2. I can get the directory path information from information supplied in a Google Sheet.

Basically what I needed to do was create a Report maker and store it in the following directory path:

My Drive(root) >> Year >>  Quarter >> Unit+ "Report"

For example:

MyDrive >> 2018 >> Q4  >> Unit 4 Report

Boring!!!! Take me to the code!!!

The Problem

Unfortunately, I could not simply change the last folder name from say, Unit 4 Report to Q4 Unit 4 Report 2018 so it is easily searchable and unique. The other problem is that there are other Unit 4 Reports in other years and quarters so I did not want to accidentally call them instead of the exact one I wanted.

So, what to do..?

The Solution

Continue reading “Google Apps Script – How to Find the Folder ID of a Non-Unique Folder Using File Path Names”

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