Introduces how to insert cells, rows, and columns in Office scripts.
Operations
Using script recordings will target the active sheet.
However, that would change the behavior depending on where the script is run, so it is recommended to specify the sheet name as well.
Change the red text accordingly.
Cell range.insert(Shift direction after insert);Shift direction after insert
Specify either down or right. The difference between the last down or rigth.
- ExcelScript.InsertShiftDirection.down
- ExcelScript.InsertShiftDirection.right
Cell Insertion
Specify by cell address
To insert cell A1 on the Test sheet, do the following
Specify the cell address with the getRange method.
// Insert in cell A1 and move down
workbook.getWorksheet("Test").getRange("A1").insert(ExcelScript.InsertShiftDirection.down);
// Insert in cell A1 and move to the right
workbook.getWorksheet("Test").getRange("A1").insert(ExcelScript.InsertShiftDirection.right);
Specified by index (sequential number starting from 0)
To insert cell A1 on the Test sheet, do the following
Specify the index in the getCell method, where the first column (column A) and the first row are both 0.
Cell A1 becomes getCell(0,0).
// Insert in cell A1 and move down
workbook.getWorksheet("Test").getCell(0,0).getCell(0, 0).insert(ExcelScript.InsertShiftDirection.down);
// Insert in cell A1 and move to the right
workbook.getWorksheet("Test").getCell(0,0).insert(ExcelScript.InsertShiftDirection.right);
Row insert
To insert the first row of the Test sheet, do the following.
workbook.getWorksheet("Test").getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);
Column insert
To insert the first column (column A) of the Test sheet, do the following.
workbook.getWorksheet("Test").getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
For those who want to learn Office script effectively
The information on this site is now available in an easy-to-read e-book format.
Or Kindle Unlimited (unlimited reading).
You willl discover how to about basic operations.
By the end of this book, you will be equipped with the knowledge you need to use Excel Office Script to streamline your workflow.