Guest User

Untitled

a guest
Jul 23rd, 2018
89
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.17 KB | None | 0 0
  1. function ReplaceQueryFiledName(queryString, srcSheetName, srcFieldRangeString) {
  2. var result = queryString;
  3. var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  4.  
  5. var sheet = null;
  6. if (typeof srcSheetName === "undefined") {
  7. sheet = spreadsheet.getActiveSheet();
  8. } else {
  9. sheet = spreadsheet.getSheetByName(srcSheetName);
  10. }
  11.  
  12. var range = null;
  13. if (typeof srcFieldRangeString === "undefined") {
  14. range = sheet.getRange(1, 1, 1, 256);
  15. } else {
  16. range = sheet.getRange(srcFieldRangeString);
  17. }
  18.  
  19. var fields = range.getValues();
  20. var maxRow = fields.length;
  21. var maxColumn = fields[0].length;
  22.  
  23. for(var row = 0; row < maxRow; row++) {
  24. for(var column = 0; column < maxColumn; column++) {
  25. var target = fields[row][column];
  26. if(target != null && target != "") {
  27. var cell = range.getCell(row + 1, column + 1);
  28. var a1 = cell.getA1Notation();
  29. var field = a1.match(/[a-zA-Z]+/);
  30. result = result.replace(/(`([^`]+)`)/g, function(match, group1, group2) {
  31. var rep = group1;
  32. if(group2 == target) {
  33. rep = field;
  34. }
  35. return rep;
  36. });
  37. }
  38. }
  39. }
  40. return result;
  41. }
Add Comment
Please, Sign In to add comment