Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function ReplaceQueryFiledName(queryString, srcSheetName, srcFieldRangeString) {
- var result = queryString;
- var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
- var sheet = null;
- if (typeof srcSheetName === "undefined") {
- sheet = spreadsheet.getActiveSheet();
- } else {
- sheet = spreadsheet.getSheetByName(srcSheetName);
- }
- var range = null;
- if (typeof srcFieldRangeString === "undefined") {
- range = sheet.getRange(1, 1, 1, 256);
- } else {
- range = sheet.getRange(srcFieldRangeString);
- }
- var fields = range.getValues();
- var maxRow = fields.length;
- var maxColumn = fields[0].length;
- for(var row = 0; row < maxRow; row++) {
- for(var column = 0; column < maxColumn; column++) {
- var target = fields[row][column];
- if(target != null && target != "") {
- var cell = range.getCell(row + 1, column + 1);
- var a1 = cell.getA1Notation();
- var field = a1.match(/[a-zA-Z]+/);
- result = result.replace(/(`([^`]+)`)/g, function(match, group1, group2) {
- var rep = group1;
- if(group2 == target) {
- rep = field;
- }
- return rep;
- });
- }
- }
- }
- return result;
- }
Add Comment
Please, Sign In to add comment