specific columns of selected row for print-CodePudding

How to set specific columns of selected row for print ? Google Apps Script - Google Sheets

Where I select a row of the google sheet, the data of these columns should be printed by the macro ("A", "B", "J", "S").

I've written this macro to select rows but I don't know how to make more changes required

function print() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();

}

CodePudding user response：

I believe your goal is as follows.

When you select a cell, you want to retrieve the values of the specific columns of the row of the selected cell.

In this case, how about the following modification?

Modified script:

function print() {
  var selectColumns = ["A", "B", "J", "S"]; // This is from your question.
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var currentRow = sheet.getCurrentCell().getRow();
  var columnIndex = selectColumns.map(letter => [...letter.toUpperCase()].reduce((c, e, i, a) => (c  = (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)), -1));
  var values = sheet.getRange(currentRow, 1, 1, sheet.getMaxColumns()).getValues().map(r => columnIndex.map(c => r[c]))[0].join(",");
  console.log(values) // Here, you can also confirm the values in the log.
  Browser.msgBox(values); // You can see the values at a dialog of Spreadsheet.
}

When you select a cell and run this script, the values of the specific columns "A", "B", "J", "S" of the row of the selected cell are retrieved.
At columnIndex, the column letters are converted to the column indexes.

From sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();, if you want to activate the specific columns, how about the following modification?

  function print2() {
    var selectColumns = ["A", "B", "J", "S"]; // This is from your question.
    var sheet = SpreadsheetApp.getActiveSheet();
    var currentRow = sheet.getCurrentCell().getRow();
    sheet.getRangeList(selectColumns.map(e => e   currentRow)).activate();
  }

Reference:

map()

Added 1:

From your following reply,

I mean when i using function print2() if selectColumns = ["A", "B", "C" ] , Cells A B C are highlighted And that's great. but when i try To Use CTRL P and And I make the settings related to print the selected cells, I only see cell c in the print output

First, in this case, it is required to select the continuous ranges like "A1:C1". When the cells "A1:C1" and "E1" are selected, only "E1" is shown. And, when the cells are selected using RangeList, the cells are selected for every cell. I thought that this might be the reason for your current issue. So, when you want to use the selected cells using the printer setting, how about the following sample script?

Sample script:

When you use this script, please set startColumn and endColumn. And, please select a cell. And, please run the script. By this, in this sample, the columns "A" to "C" are selected. When you push CTRL P and do the settings related to print the selected cells, you can see the 3 cells.

function print3() {
  var startColumn = "A"; // Please set the start column.
  var endColumn = "C"; // Please set the end column.

  var sheet = SpreadsheetApp.getActiveSheet();
  var currentRow = sheet.getCurrentCell().getRow();
  sheet.getRange(`${startColumn}${currentRow}:${endColumn}${currentRow}`).activate();
}

Added 2:

If you want to use the discrete columns and cells, how about the following sample script?

Sample script:

This script uses Sheets API. So, please enable Sheets API at Advanced Google services.

When you use this script, please set selectColumns. In this case, you can set the discrete columns. And, please select a cell, and run the script of print4. By this, only the columns of selectColumns of the selected row are shown. By this, when you push CTRL P, you can see the showing cells. This is another workaround for achieving your goal.

After your work is finished, when you run showAll, all rows and columns are shown.

function print4() {
  var selectColumns = ["A", "B", "J", "S"]; // This is from your question.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var currentRow = sheet.getCurrentCell().getRow();
  var columnIndex = selectColumns.map(letter => [...letter.toUpperCase()].reduce((c, e, i, a) => (c  = (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)), -1));

  var sheetId = sheet.getSheetId();
  var requests = [...Array(sheet.getMaxColumns())].reduce((ar, _, i) => {
    if (!columnIndex.includes(i)) {
      ar.push({ updateDimensionProperties: { range: { sheetId, startIndex: i, endIndex: i   1, dimension: "COLUMNS" }, properties: { hiddenByUser: true }, fields: "hiddenByUser" } });
    }
    return ar;
  }, []);
  var maxRows = sheet.getMaxRows();
  if (currentRow == 1) {
    requests.push({ updateDimensionProperties: { range: { sheetId, startIndex: currentRow, endIndex: maxRows, dimension: "ROWS" }, properties: { hiddenByUser: true }, fields: "hiddenByUser" } });
  } else if (currentRow == maxRows) {
    requests.push({ updateDimensionProperties: { range: { sheetId, startIndex: 0, endIndex: maxRows - 1, dimension: "ROWS" }, properties: { hiddenByUser: true }, fields: "hiddenByUser" } });
  } else {
    requests.push({ updateDimensionProperties: { range: { sheetId, startIndex: 0, endIndex: currentRow - 1, dimension: "ROWS" }, properties: { hiddenByUser: true }, fields: "hiddenByUser" } });
    requests.push({ updateDimensionProperties: { range: { sheetId, startIndex: currentRow, endIndex: maxRows, dimension: "ROWS" }, properties: { hiddenByUser: true }, fields: "hiddenByUser" } });
  }
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

function showAll() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  const requests = [{ updateDimensionProperties: { range: { sheetId: sheet.getSheetId(), dimension: "COLUMNS" }, properties: { hiddenByUser: false }, fields: "hiddenByUser" } }, { updateDimensionProperties: { range: { sheetId: sheet.getSheetId(), dimension: "ROWS" }, properties: { hiddenByUser: false }, fields: "hiddenByUser" } }];
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}