flutterdartgoogle-sheets-apiget-request

How to accept variable amount of parameters in Google Sheets


I'm building a solution in which the amount of parameters added to an HTTP get request is variable. This means the URL can carry 1 to n amount of parameters. The parameters in the URL are identified with a standard name and attached index. Here is a sample URL:

https://script.google.com/macros/s/xxxxxxx/exec?fileName=2020-12-15&fridgeItems=2&fridgeItem0=ValueOfFridgeItem0&fridgeItem1=ValueOfFridgeItem1

How do I interpret the variable amount of Parameters in my doGet() Function in .gs?

EDIT: I want to create with this request the header of a new google sheet. The variable amount of parameters is supposed to be the column headers. E.g. if the request contains 5 parameters, I want to add in the first row of the google sheet into columns 1 to 5 the respective values of the parameters.

I tried the following code but it is not working:

function doGet(request) {
var FileNameString = request.parameter.fileName; //name of the new .gs file
var FridgeItems = request.parameter.fridgeItems; //amount of parameters/column headers passed to .gs

var sheet = SpreadsheetApp.create(FileNameString);

   for(var i=0; i<FridgeItems ; i++){
   sheet.getRange(1,i+2)
   .setValues(
     sheet.appendRow([request.parameter.fridgeItem&i]);
     );
   }
 }
}

Solution

  • Goal:

    When a GET request is made, you want to create a new spreadsheet whose name will be the value of the request parameter key fileName, and all the other values from all the other request parameters will be appended to the only sheet of the created spreadsheet.

    Issues:

    Solution:

    Code snippet:

    function doGet(request) {
      const parameters = request.parameter;
      const fileName = parameters.fileName;
      delete parameters.fileName;
      const headers = Object.values(parameters);
      const spreadsheet = SpreadsheetApp.create(fileName);
      spreadsheet.appendRow(headers);
      return ContentService.createTextOutput("Headers appended!");
    }
    

    Edit:

    If you want to make sure the headers are set in the same order as in the query, you can use request.queryString instead, which returns the query string portion of the URL, so in this case would be like:

    fileName=2020-12-15&fridgeItems=2&fridgeItem0=ValueOfFridgeItem0&fridgeItem1=ValueOfFridgeItem1
    

    You could then use a combination of map and filter in order to retrieve the values (excluding the one corresponding to fileName):

    function doGet(request) {
      const headers = request.queryString.split("&")
                                            .map(param => param.split("="))
                                            .filter(param => param[0] !== "fileName")
                                            .map(param => param[1]);
      const fileName = request.parameter.fileName;
      const sheet = SpreadsheetApp.create(fileName)
      sheet.appendRow(headers);
      return ContentService.createTextOutput("Headers appended!");
    }