CODEWITHSUNDEEP
google-apps-script
Dmitriy Rudakov
Dmitriy Rudakov

Reputation: 109

Values from Ranges by named ranges to variables

I have many named ranges in Spreadsheet.

I need get values from areas by current row (e) and current column by namedranges and write it to variables. I write a lot of the same script

function sendEmailClerck(e){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Ответы на форму (1)');

  var idRow = e.range.getRow();

var name = sheet.getRange(idRow, SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ReqUser').getColumn()).getValue();
var cli = sheet.getRange(idRow, SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ReqCLI').getColumn()).getValue();
var priority = sheet.getRange(idRow, SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ReqPriority').getColumn()).getValue();
var date = sheet.getRange(idRow, SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ReqDateStart').getColumn()).getValue();
var date2 = sheet.getRange(idRow, SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ReqDateCloseU').getColumn()).getValue();
var tip = sheet.getRange(idRow, SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ReqType').getColumn()).getValue();
var users = sheet.getRange(idRow, SpreadsheetApp.getActiveSpreadsheet().getRangeByName('ReqAddress').getColumn()).getValue();

I found one script in this site but I can't understand how can I use it here

function getRangeName(A1Notation) {
    var rangeName = "";
    //get all the name ranges of the sheet
    var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getNamedRanges();
    // loop on all the names range of the sheet
    for(i=0;i<namedRanges.length;i++){
        //if it's the same A1 Notation
        if(namedRanges[i].getRange().getA1Notation() == A1Notation)
            rangeName = namedRanges[i].getName();
    }
    //will return the name of the range or "" if there is no range
    return rangeName;
}

I think that's it I need. I think I can write to variables the values by put the value in parameter and send it to one function and return it back. Can you help me this this?

Thank you for your help!

Upvotes: 0

Views: 153

Answers (1)

doubleunary
doubleunary

Reputation: 18784

Try Array.map() to make this a bit more manageable, like this:

function sendEmailClerck(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Ответы на форму (1)');
  const rangeNames = [
    'ReqUser',
    'ReqCLI',
    'ReqPriority',
    'ReqDateStart',
    'ReqDateCloseU',
    'ReqType',
    'ReqAddress',
  ];
  const [name, cli, priority, date, date2, tip, users] = rangeNames.map(rangeName => {
    const column = sheet.getRange(rangeName).getColumn();
    return sheet.getRange(e.range.rowStart, column).getValue();
  });
}

You may want to rethink the whole approach though. The code is very inefficient because it reads every cell one by one. You should probably use just one sheet.getDataRange().getValues() call and parse the data from the the 2d array it returns.

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info, Mozilla Developer Network and Apps Script at Stack Overflow.

Upvotes: 1

Related Questions