CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
k.b
k.b

Reputation: 165

How can I adjust row width & column width of google sheet dynamically using apps script?

I am about to design a structure in Google sheet. How can I adjust row width & column width dynamically using apps script?

Note: To adjust the width of column or row, I mean giving width some pixels or inch size. I don't mean auto-size to fit content in the cells.

Upvotes: 5

Views: 14325

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I believe your goal as follows.

  • You want to dynamically use the auto resize for the edited row and column using Google Apps Script.

In this case, how about running the script using the simple trigger of OnEdit? By this, when you put a value to a cell, the script is automativally run. And the auto resize for the row and column can be achieve using autoResizeRows and autoResizeColumns. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the project. When you use this script, please put a value to a cell. By this, the script is run by the trigger and the row height and column width are automatically set.

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  sheet
    .autoResizeRows(range.rowStart, range.rowEnd - range.rowStart + 1)
    .autoResizeColumns(range.columnStart, range.columnEnd - range.columnStart + 1);
}

Note:

  • This script is run by the simple trigger of OnEdit using the event object. So when you directly run the script with the script editor, an error occurs. Please be careful this.

  • This is a simple sample script. So please modify this for your actual situation. For example, when you want to run the script at the specific sheets of "Sheet1" and "Sheet2", please put the script of if (!["Sheet1","Sheet2"].includes(sheet.getSheetName())) return; after the line of const sheet = range.getSheet();.

References:

Added:

From the following your replying,

I do not mean auto-size, When we resize the column we can actually enter pixel as width manually. I want to resize using the apps script parameter to set column width pixel value.

I added one more sample script for this.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and save the project. When you use this script, please put a value to a cell. By this, the script is run by the trigger and the column width is changed to columnWidth.

function onEdit(e) {
  const columnWidth = 300; // Please set the column width you want to set.
  const range = e.range;
  const sheet = range.getSheet();
  // if (!["Sheet1","Sheet2"].includes(sheet.getSheetName())) return; // If you want to run the script for the specific sheet, please use this line.
  sheet.setColumnWidths(range.columnStart, range.columnEnd - range.columnStart + 1, columnWidth);
}

Reference:

Upvotes: 12

Related Questions