如何使用Google脚本中的URL参数过滤Google表格列中的数据? [英] How to filter data in Column Google Sheets with URL Parameter in Google Script?

问题描述

我正在尝试Google脚本，我想显示基于关键字参数的数据，该数据稍后将C列称为数据过滤器，将根据在参数关键字中搜索的内容进行显示.

I'm trying Google Script and I want to display data based on keyword parameters which will later refer to Column C as a data filter which will be displayed according to what is searched for in the parameter keyword.

例如在我之前的项目中，我有一个参数作为分页

For example in my previous project I had a parameter as pagination

?page = 1& limit = 10

现在我要解决一个新问题，即搜索和分页，我将b列作为搜索的参考

well now I have a new problem to solve which is both search and pagination, I have column b as the reference for the search

?page = 1& limit = 10& search =圣安地列斯

如何实现以下代码?

function doGet(e) {
  var page = e.parameter.page || 1;
  var limit = e.parameter.limit || 10;
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111");
  var sheet = ss.getSheetByName("list_film");
  return getUsers(sheet, page, limit); 
}

function getUsers(sheet, page, limit){
  var rows = sheet.getDataRange().getValues();
  var dataArray = rows.splice(limit * (page - 1), limit).reduce((ar, [a, b, c, d]) => ar.concat({id: a, year: b, title: c, img: d}), []);
  var jo = {};
  jo.user = dataArray;
  var result = JSON.stringify(jo);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

我的意思是C列

编辑以解决:

我想对@Tanaike表示感谢，他非常帮助我！

I want to say thank you to @Tanaike who helped me so hard!

这是我的最终代码

function doGet(e) {
 var page = e.parameter.page || 1;
  var limit = e.parameter.limit || 10;
  var search = e.parameter.search || "";
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111");
  var sheet = ss.getSheetByName("list_film");
  return getUsers(sheet, page, limit, search);
}

function getUsers(sheet, page, limit, search){
  var lowCase = search.toString().toLowerCase();
  var rows = sheet.getDataRange().getValues().filter(([,,c]) => c.toString().toLowerCase().includes(lowCase));
  var dataArray = rows.splice(limit * (page - 1), limit).reduce((ar, [a, b, c, d]) => ar.concat({id: a, year: b, title: c, img: d}), []);
  var jo = {};
  jo.user = dataArray;
  var result = JSON.stringify(jo);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

推荐答案

我相信您的目标如下.

• 您想从工作表"list_film"中检索值.电子表格 SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111").
• 您要通过从"B"列中搜索 search 的值来检索值.

• You want to retrieve the values from the sheet "list_film" of Spreadsheet SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111").
• You want to retrieve the values by searching the value of search from the column "B".

• 从 e.parameter.page ， e.parameter.limit 和?page = 1& limit = 10& search =圣安德烈亚斯在您的脚本中，我了解到您正在使用Web Apps.
• 在这种情况下，需要使用?page = 1& limit = 10& search = San Andreas 中的 San Andreas ，例如 e.parameter.搜索.
• 我想建议通过添加 filter 来修改 getUsers 中的 sheet.getDataRange().getValues().就像 sheet.getDataRange().getValues().filter(([[，b])=> b ==搜索)

• From e.parameter.page, e.parameter.limit and ?page=1&limit=10&search=San Andreas in your script, I understood that you are using Web Apps.
• In this case, it is required to use San Andreas of ?page=1&limit=10&search=San Andreas like e.parameter.search.
• I would like to propose to modify sheet.getDataRange().getValues() in getUsers by adding filter. It's like sheet.getDataRange().getValues().filter(([,b]) => b == search)

当以上几点反映到您的脚本中时，它如下所示.

When above points are reflected to your script, it becomes as follows.

  var page = e.parameter.page || 1;
  var limit = e.parameter.limit || 10;
  var search = e.parameter.search || "";
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111");
  var sheet = ss.getSheetByName("list_film");
  return getUsers(sheet, page, limit, search); 
}

function getUsers(sheet, page, limit, search){
  var rows = sheet.getDataRange().getValues().filter(([,b]) => b == search);
  var dataArray = rows.splice(limit * (page - 1), limit).reduce((ar, [a, b, c, d]) => ar.concat({id: a, year: b, title: c, img: d}), []);
  var jo = {};
  jo.user = dataArray;
  var result = JSON.stringify(jo);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

• 在这种情况下，当它请求 https://script.google.com/macros/s/###/exec?page=1&limit=10&search=圣安德烈亚斯时，从列"B"中搜索 San Andreas .表"list_film"并返回与 San Andreas 相同的行.
  • 如果要检索包括 search 的值，请使用 b.toString().includes(search)而不是 b == search .
  • In this case, when it requests to https://script.google.com/macros/s/###/exec?page=1&limit=10&search=San Andreas, San Andreas is searched from the column "B" of sheet "list_film" and the rows, which are the same with San Andreas, are returned.
    • If you want to retrieve the values including search, please use b.toString().includes(search) instead of b == search.

    • 修改了Web Apps的脚本后，请重新部署Web Apps作为新版本.这样，最新脚本将反映到Web应用程序中.请注意这一点.

    尽管我不确定列"B"的值，但是如果上述脚本不是您期望的结果，请尝试修改 sheet.getDataRange().getValues().filter(([[，b])=> b ==搜索)到 sheet.getDataRange().getDisplayValues().filter(([[，b])=> b ==搜索)并再次进行测试.

    Although I'm not sure about the values of column "B", if above script was not the result you expect, please try to modify sheet.getDataRange().getValues().filter(([,b]) => b == search) to sheet.getDataRange().getDisplayValues().filter(([,b]) => b == search) and test it again.

    例如，如果您想与其他列而不是"B"列进行比较，例如，当您想将 search 的值与"C"列进行比较时，请将 filter(([[，b])=> b ==搜索)修改为 filter(([,, c])=> c == search)

    If you want to compare with other column instead of the column "B", for example, when you want to compare the value of search with the column "C", please modify filter(([,b]) => b == search) to filter(([,,c]) => c == search).

    • filter()
    • includes()

    • 从您的 我的最后一个代码 如下所示我建议的脚本未正确使用.

    • From your my last code as follows, it was found that my suggested script was not correctly used.

       function doGet(e) {
       var page = e.parameter.page || 1;
        var limit = e.parameter.limit || 10;
        var search = e.parameter.search || "";
        var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111");
        var sheet = ss.getSheetByName("list_film");
        return getUsers(sheet, page, limit, search); 
      }
    
      function getUsers(sheet, page, limit, search){
        var rows = sheet.getDataRange().getDisplayValues().filter(([,b]) => b == search);
        var rows = sheet.getDataRange().getValues().slice(1);
        var rows = sheet.getDataRange().getValues().reverse();
        var dataArray = rows.splice(limit * (page - 1), limit).reduce((ar, [a, b, c, d]) => ar.concat({id: a, year: b, title: c, img: d}), []);
        var jo = {};
        jo.user = dataArray;
        var result = JSON.stringify(jo);
        return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
      }
    

    • 在 var行= sheet.getDataRange().getValues().slice(1); 和 var行= sheet.getDataRange().getValues().reverse(); ，使用 sheet.getDataRange().getValues()检索值.这样，就不会过滤值.我认为这是您当前问题的原因.

    • At var rows = sheet.getDataRange().getValues().slice(1); and var rows = sheet.getDataRange().getValues().reverse();, the values are retrieved using sheet.getDataRange().getValues(). By this, the values are not filtered. I think that this is the reason of your current issue.

      修改当前脚本后，它如下所示.

      When your current script is modified, it becomes as follows.

      function doGet(e) {
       var page = e.parameter.page || 1;
        var limit = e.parameter.limit || 10;
        var search = e.parameter.search || "";
        var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/10S8Igzt1tpqUBgJHVKpny-2s6G3Y3-vFsLMvlZVqpkc/edit#gid=810612111");
        var sheet = ss.getSheetByName("list_film");
        return getUsers(sheet, page, limit, search);
      }
      
      function getUsers(sheet, page, limit, search){
        var rows = sheet.getDataRange().getDisplayValues().filter(([,,c]) => c == search);
        var dataArray = rows.splice(limit * (page - 1), limit).reduce((ar, [a, b, c, d]) => ar.concat({id: a, year: b, title: c, img: d}), []);
        var jo = {};
        jo.user = dataArray;
        var result = JSON.stringify(jo);
        return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
      }
      

      • 如果要删除第一个标题行并返回相反的值，请按如下所示修改函数 getUsers .

          function getUsers(sheet, page, limit, search){
            var rows = sheet.getDataRange().getValues().slice(1).filter(([,,c]) => c == search).reverse();
            var dataArray = rows.splice(limit * (page - 1), limit).reduce((ar, [a, b, c, d]) => ar.concat({id: a, year: b, title: c, img: d}), []);
            var jo = {};
            jo.user = dataArray;
            var result = JSON.stringify(jo);
            return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
          }
        

      这篇关于如何使用Google脚本中的URL参数过滤Google表格列中的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！