CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheets
Landsil
Landsil

Reputation: 13

Apps Script - read column and put in JSON and string

Task:

Column full of data (unknown length) eg.

|data1 |
|data2 |
|data3 |

I need to convert this into JSON ( and then do other things but I know how )

{
  "adminSettings":
  { "Value":
    {"whitelist":
      [
        "data1",
        "data2",
        "data3"
      ],
      "netWhitelist":"data1\ndata2\ndata3"
    }
  }
}

Expected way:

If I understand correctly I should be able to specify most of this JSON in code and then load column into script and then

  1. Loop thru array it will create and for every cell add value to whitelist as key.
  2. Loop thru same array and take values and merge into string adding "\n" in between and then add as value to netWhitelist

I've assembled this but it's far from finished and most manuals are about how to take data from JSON and put in a sheet, not other way around.

function make_JSON() {
  // Read data in column
  var AUTO_data = activeSpreadsheet.getSheetByName("AUTO_data");
  var values =  AUTO_data.getRange("A2:A").getValues();
  var JSON = {"adminSettings":
              {"Value":
               {"whitelist":
                [

                ],
                "netWhitelist": netWhitelist_string
               }
              }
             }
}

Any tips and examples will help, I would prefer to understand and resolve it on my own.

Upvotes: 1

Views: 121

Answers (1)

TheMaster
TheMaster

Reputation: 50855

getValues() returns a 2D array. Flatten it to a 1D array and add it to json (Array.join to make a string):

function make_JSON() {
  // Read data in column
  var AUTO_data = activeSpreadsheet.getSheetByName("AUTO_data");
  var values =  AUTO_data.getRange("A2:A"+AUTO_data.getLastRow()).getValues().flat();
  var JSON = {"adminSettings":
              {"Value":
               {"whitelist":values,
                "netWhitelist": values.join('\n')
               }
              }
             }
}

Upvotes: 2

Related Questions