Google Sheets is a versatile tool, offering a myriad of features and formulas to help compile, calculate, and analyze data. One such feature is the ability to manipulate text through various formulas. A common request is to remove the first character from a cell. This might seem baffling if you’re not familiar with Google Sheets or its guardian language – Google Apps Script, which uses a variation of Javascript, similar to Typescript. But don’t worry, in the post, we’ll walk you through how to do it, explaining the steps clearly and concisely.
function removeFirstCharacter(sheetName: string) {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
let range = sheet.getDataRange();
let values = range.getValues();
values = values.map(row => row.map(cell => typeof cell === 'string' ? cell.substring(1) : cell));
range.setValues(values);
}
The function above, `removeFirstCharacter`, takes in a single argument – sheetName. This is the name of the sheet in the Google Sheets document from which you want to remove the first character.
Understanding the Code
Firstly, we get a reference to the sheet by calling `SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)`. Then, we get a Range object representing all the cells in the sheet by calling `getDataRange()` method.
The `getValues()` method returns a two-dimensional array, with each sub-array representing a row in the sheet.
We then use the `map` function twice, once for the outer array (rows) and once for each inner array (cells in a row). For every cell that is a string, we call the `substring()` method, with 1 as the index to start from, effectively removing the first character.
Finally, we use `range.setValues(values)` to write the updated data back to the sheet.
Google Apps Script and its relevance to Typescript
Google Apps Script, the language Google Sheets formulas are written in, and Typescript, both have their roots in Javascript. In fact, Typescript is a superset of Javascript, extending the language with static types, which are beneficial in large code bases, where increased safety and tooling are needed.
Google Apps Script is based on Javascript 1.6, with some portions of 1.7 and 1.8 and provides simpler ways to automate tasks across Google products.
Dimension and Array Manipulation in Typescript
In the code written above, we utilized multidimensional arrays (an array of arrays), which is a common data structure used in many programming languages, including Typescript.
What makes Typescript’s take on this classic data structure unique is its type checking ability. With Typescript, you can ensure your arrays, even multidimensional ones, contain data of a specific type.
With Typescript’s type safety and Google Apps Script’s simplicity, you can ethically architect accurate, large-scale applications, fostering efficiency and expertise in your programming toolkit. It’s this synergy between simplicity and scale that underpins much of Google Sheets and its Text Manipulation Formulas.
Array.method.map()
In the realm of programming, the Array.prototype.map() is not an alien function. It’s common among programmers due to its utility in creating a new array populated with the results of calling a given function on every element in the array. Likewise, in our code, we have deployed the array.map() function for array manipulation – creating a new array by removing the first character from each string element.
