November 2019 - Yagisanatode

Google Apps Script: Filling out your column data to match the number of columns in a range

Google Apps Script, Google Sheets

Have you ever experienced this warning error when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script?

The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 9. (line 52, file “test”)

Yeap. One of your row lengths is not as long as the width of the column range you set.

This generally occurs when you are manipulating data into a new format before setting it back into your Google Sheet.

Let’s look like some dodgy data I’ve prepared that I want to paste or set backing my spreadsheet.

var dodgy2dArray = [
  [1,2,3,4,5,6],
  [1,2,3,4,5,6,7,8,9],
  [1,2,3,4,5,6],
  [1,2,3],
  [1,2,3,4,5,6,7,8],
  [1,2,3,4,5,6,7,8,9],
  [1]
];

var dodgy2dArray = [

[1,2,3,4,5,6],

[1,2,3,4,5,6,7,8,9],

[1,2,3,4,5,6],

[1,2,3],

[1,2,3,4,5,6,7,8],

[1,2,3,4,5,6,7,8,9],

[1]

];

As you can see, I’ve got a 2d array for data with 7 rows and with a max row width of 9. This means our data will extend to nine columns at its max.

Now, your first instinct might be that if I take a few chips out of square peg, it should still fit into a square hole. I mean, I’m only subtracting from the square, right?

Then you go off blazing a path through the code, hoody hanging low over your head, punching away at your bespoke mechanical keyboard.

function stoopid(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange(1,1,7,9);
  range.setValues(dodgy2dArray);
};

function stoopid(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getActiveSheet();

var range = sheet.getRange(1,1,7,9);

range.setValues(dodgy2dArray);

};

With victory within your grasp, you hit run and your world comes crashing down.

The Solution

Continue reading “Google Apps Script: Filling out your column data to match the number of columns in a range”

How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Google Sheets, Google Apps Script: SpreadsheetApp, PropertiesService, Binary Search, onOpen, Button

Ever had to open a huge data entry spreadsheet an all you want to do is enter your data and move onto another task? Instead, you have to waste precious time navigating all the way down to the bottom of the page to add your data.

Boo!!!

Wouldn’t it be cool to just jump down to the first empty row and get clicky-clacking away?

Behold, dear biped! This friendly coding goat has a little gift for you.

In this tutorial, we are going to tackle two ways to automatically move down to the first available empty cell. One will be super easy and the other slightly more tricky, fun and versatile (You can thank one of the Google Apps Script outreach gurus for the inspiration for this one).

We’ll show you how to set up this little code so it can be run via a button at the top of your page or when you open the Google Sheet workbook each time.

Note! As always feel free to read the entire tutorial or take what you need. There is a quick start guide for those people who just want to copy and paste into their own projects and a deep dive for those who want to understand the code.

Scenario 1: The Super Easy Way
Scenario 2: The more difficult, but more useable way
- The Code
  - Quick Use
  - Code Breakdown
Conclusion

Scenario 1: The Super Easy Way

Let’s say we have a simple data set that the user needs to enter a new row of data each time the open the Google Sheet workbook.

Something like this:

Continue reading “How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script”

Month: November 2019

Google Apps Script: Filling out your column data to match the number of columns in a range

The Solution

Like this:

How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Contents

Scenario 1: The Super Easy Way

Like this:

The Solution

Share this:

Like this:

Contents

Scenario 1: The Super Easy Way

Share this:

Like this: