Tuesday 18 December 2018

Get specific sheet data - Improved

The following Google Apps Script code is an improved version of getting specific sheet data to translate onto another sheet (Rookie version). The code makes use of arrays to achieve this more efficiently by getting all of the data from the sheet in one go.

This time once the active spreadsheet/sheet/cell, row and last column number are found, an array of all values on the current row is created ('getRange'). This is a 1-D array which means it only has 1 set of square brackets as it is only for 1 row in this instance. Now the 4 variables can be created from the array by assigning them the position of the relevant string in the array (eg 'var postcode = theData[3]'). From here we create a 2-D array as the destination will be a 2-D array also (as it covers more than 1 row). At the same time as getting the destination array we can assign the relevant values all in one go ('setValues'), as we do in the final line of the script.

Get SpecificSheetData - Improved.xlsx

No comments:

Post a Comment