javascript - 用于在 Google 表格中分解查询参数的 Google 应用脚本

Question

我有一个 Google 表格，其中包含 100 个带有查询参数的 https 请求网址。 URL 如下所示: https://122.2o7.net/b/ss/ryan1/1/JS-2.0.0/s12345678?AQB=1&ndh=1&pf=1&t=6%2F9%2F2018%208%3A48%3A34%206%20360&ts=1538837314190&vid=test&fid=1w23232-erwwwre&ce=UTF-8&ns=ryan&pageName=ryan%3Atest%3Apage&g=https%3A%2F%2Fryanpraski.com%2F&cc=USD&ch=home&events=event1&c1=D%3Dv1&v1=evar1value&h1=hier1value&v20=evar20value&bh=8&AQE=1

我想使用 Google App Script 分解查询参数并将它们整齐地放入 Google Sheet 中，如下所示:

我得到了下面的代码来打破查询字符串并用 & 分隔符分割查询字符串参数，但我不知道接下来要做什么。

我还需要考虑几个案例。

1. 可能有些网址的参数比我的示例网址更多或更少，但总会有一些重叠。我希望列标题自动更新。
2. 可能存在诸如 c1=D%3Dv1 之类的值，其中解码后的值为 c1=D=v1

任何帮助将不胜感激!

function test() {
var url = "https://122.2o7.net/b/ss/ryan1/1/JS-2.0.0/s12345678?AQB=1&ndh=1&pf=1&t=6%2F9%2F2018%208%3A48%3A34%206%20360&ts=1538837314190&vid=test&fid=1w23232-erwwwre&ce=UTF-8&ns=ryan&pageName=ryan%3Atest%3Apage&g=https%3A%2F%2Fryanpraski.com%2F&cc=USD&ch=home&events=event1&c1=D%3Dv1&v1=evar1value&h1=hier1value&v20=evar20value&bh=8&AQE=1";
var cleanUrl = decodeURIComponent(url);
var params = cleanUrl.split('?')[1];
var s = params;
var t = s.split('&');
var output = [];
t.forEach(function(q) {
    output.push([q]);
});
Logger.log(output);

}

Answer 1

以下代码分解查询参数并将它们放入特定的工作表中。它还解决了一些可能的情况:
1 现有代码不匹配。在这种情况下，将输入一个空格作为占位符。
2 URL 包含现有列表中未包含的代码。在这种情况下，"new"代码将添加到列表中，并且它们的值也会被记录。
3 正如提问者指出的，某些 URL 参数包含多个“等于”符号(“=”)。在这种情况下不能使用 Split，因为尽管可以使用参数来限制找到的拆分数量，但剩余文本不会在新数组中返回。所以我使用indexOf(它返回searchValue第一次出现的索引)和subString来计算URL组件的两部分。

我假设现有的代码列表位于 Row1 中，因此我创建了一个 NamedRange 来管理它们。如果代码发现 URL 参数与命名范围中的代码不匹配，则删除并重新创建命名范围以包含"new"代码。

代码将结果输出到电子表格中的“第三个工作表”(ss.getSheets() 2 ;)；这是可以改变的。确定包含数据的最后一行，并将分析结果设置在下一行

注意:url 是硬编码的。

<小时/>

function so_52825789() {


    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var datasheet = ss.getSheets()[2];

    // the codes are in Row1 in a Named Range called DataVariables
    var urlvars = ss.getRangeByName('DataVariables').getValues();

    // get the number of Columns for the Named Range
    var datalen = urlvars[0].filter(String).length;
    //Logger.log("Number of columns of codes = "+datalen); //DEBUG

    //get the last row of containing data
    var lastvals = ss.getRange("A1:A").getValues();
    var lastrow = lastvals.filter(String).length;
    //Logger.log("the last row is "+lastrow);// DEBUG

    // Get the url
    var url = "https://122.2o7.net/b/ss/ryan1/1/JS-2.0.0/s12345678?AQB=1&ndh=1&pf=1&t=6%2F9%2F2018%208%3A48%3A34%206%20360&ts=1538837314190&vid=test&fid=1w23232-erwwwre&ce=UTF-8&ns=ryan&pageName=ryan%3Atest%3Apage&g=https%3A%2F%2Fryanpraski.com%2F&cc=USD&ch=home&events=event1&c1=D%3Dv1&v1=evar1value&h1=hier1value&v20=evar20value&bh=8&AQE=1&ejb=1";

    // Javascript function to remove the URL encoded charaters
    var cleanUrl = decodeURIComponent(url);

    // get the URL variables (the second half of the split)
    var params = cleanUrl.split('?')[1];
    var s = params;

    // get the specific query variables by spliting on "&"
    var t = s.split('&');

    // get the number of query variables
    var tlen = t.filter(String).length;

    // setup some variables for use later
    var output = [];
    var mismatchcode = [];
    var mismatchdata = [];
    var tcount = [];
    var nomatch = 0;
    var ttest = 0;
    var ztest = 0;

    // Loop through the known codes from the Named Range
    for (i = 0; i < datalen; i++) {

        // set a variable value so that you can count how many named codes were found
        ttest = 1;

        // Start a loop though the query variables in the URL
        for (z = 0; z < tlen; z++) {

            // get the position of the Equals sign "="; there may be more than one but we only want the first one.
            var n = t[z].indexOf("=");
            if (n > 0) {
                //var result="Equals appears at position = "+(n+1)+" (actual value = "+n+")";
                //Logger.log(result);

                //get the length of the element
                var nstr = t[z].length;
                //Logger.log("Length = "+nstr);  //DEBUG

                // break the element into two halves. The first half is the "Code" and the second half is the "value"
                var code = t[z].substring(0, n);
                var codevalue = t[z].substring((n + 1), nstr);
                //Logger.log("z = "+z+", code is = "+code+", and the value is "+codevalue); // DEBUF
            }

            // test to whether there is a match between the Named Range Code and the URL 
            if (urlvars[0][i] == code) {

                // set the variable to note a match was detected.
                ttest = 0;
                // push the code value into an array
                output.push(codevalue);
                // push the Named range code ID onto an array
                tcount.push(z);
                //Logger.log("Match "+urlvars[0][i]+" = "+code); //DEBUG
            }


        } // end of the URL variables loop

        // having looped through the URL variables, test to see whether there was a match
        // if not (ttest still equals One) then put an empty string in the output array, so ensure that every code has a value
        // and keep count of the number of "nomatches"
        if (ttest == 1) {
            output.push(" ");
            Logger.log("No match for " + urlvars[0][i]);
            nomatch = nomatch + 1;
        }
    } // end of the Named Range loop

    // create an array for 2d format
    var outeroutput = [];
    // put the loop array into the blank array. The result is a 2d array that can be read by the Google sheets script.
    outeroutput.push(output);
    //  For the NamedRange analysis, we can now set the values from the loop
    var targetrange = datasheet.getRange(lastrow + 1, 1, 1, datalen);
    targetrange.setValues(outeroutput);
    //Logger.log("targetrange = "+targetrange.getA1Notation()); //DEBUG



    // count how matches were found for URL variables
    var tcountlen = tcount.filter(String).length;
    // compare the number of variables in the URL with the number of matches. 
    // If there is a difference, then we need to loop through the URL variables, find the ones that didn't match and do stuff with them.
    if ((tlen - tcountlen) > 0) {
        // starp loop for URL variables
        for (z = 0; z < tlen; z++) {
            // set the variable to detect whether or not a a match was made.
            ztest = 1;
            // Repeat the process of splitting the component code and value
            var n = t[z].indexOf("=");
            if (n > 0) {
                // get the length of the variable
                var nstr = t[z].length;
                // get the componet parts
                var code = t[z].substring(0, n);
                var codevalue = t[z].substring((n + 1), nstr);
                //Logger.log("z = "+z+", code is = "+code+", and the value is "+codevalue); //DEBUG
            }

            // start the loop for thecodes in the NamedRange
            for (i = 0; i < datalen; i++) {

                // If there's a match, chnage the value of the 'match testing' varuable
                if (urlvars[0][i] == code) {
                    ztest = 0;
                }
            } // end of the loop for NamedRange codes

            // if there hasn't been match, then
            // push the url variable code and value onto some respective arrays
            if (ztest == 1) {
                mismatchcode.push(code);
                mismatchdata.push(codevalue);
            }
        } // end of the URL variables loop


        //Logger.log("Code fields = "+datalen+", data fields = "+tlen);// DEBUG
        //Logger.log("Total no-matches for codes = "+nomatch);  // DEBUG
        // Logger.log("Total no-matches for URL fields = "+(tlen-tcountlen)); //DEBUG

        // So, what shall we do if there the number of variables in the NAMED RANGE does equal the number of variables 
        //  if((tlen-tcountlen) !=0){

        // These rows are just for DEBUG assignstance.
        // for (i=0;i<(tlen-tcountlen);i++){ //DEBUG
        // Logger.log("URL field not found: code = "+mismatchcode[i]+", value = "+mismatchdata[i]); //DEBUG
        // } //DEBUG

        // create the arrays to act as 2d
        var outermismatchcode = [];
        var outermismatchdata = [];

        // Push the mismatch arrays to the create the 2d arrays
        outermismatchcode.push(mismatchcode);
        outermismatchdata.push(mismatchdata);

        // Identify the range for the addition URL Codes and values
        // set the respective values
        var extraurlcoderange = datasheet.getRange(1, datalen + 1, 1, (tlen - tcountlen));
        extraurlcoderange.setValues(outermismatchcode);
        var extraurldatarange = datasheet.getRange(lastrow + 1, datalen + 1, 1, (tlen - tcountlen));
        extraurldatarange.setValues(outermismatchdata);

        // We want to add the "new" codes found in the URL to the Named Range.
        // Start by deletinging the existing NamedRange
        ss.removeNamedRange("DataVariables");

        // Define the parmeters for a new range.
        // The main thing is that we need to add more columns
        var newnamedrange = datasheet.getRange(1, 1, 1, (datalen + (tlen - tcountlen)))

        // So, Create a new NamedRange using the same name as before.
        ss.setNamedRange('DataVariables', newnamedrange);

        // The following lines are just to check that everything worked OK
        // var rangeCheck = ss.getRangeByName("DataVariables"); // DEBUG
        // if (rangeCheck != null) { //DEBUG
        //  Logger.log("Columns in the new named range = "+rangeCheck.getNumColumns());//DEBUG
        // }  ,//DEBUG
        // var rangeCheckName = rangeCheck.getA1Notation();  //DEBUG
        // Logger.log("the new named range is  = "+rangeCheckName);//DEBUG    


    } // end of the loop to identify URL variables that didn't match a code in the NamedRange    
}

<小时/>

请注意，c1 代码的加法值包含相关的等号。此外，该 URL 还包含一个不在现有列表中的附加参数(“ejb=1”)；该代码及其值将添加到电子表格中，NamedRange 现在包含"new"代码。