Apps Script - read column and put in JSON and string

Question

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

Loop thru array it will create and for every cell add value to whitelist as key.
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.

Benjamin Loison · Accepted Answer · 2023-07-04 23:57:03Z

2

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')
               }
              }
             }
}

edited Jul 4, 2023 at 23:57

Benjamin Loison

5,7504 gold badges20 silver badges37 bronze badges

answered Apr 21, 2020 at 18:06

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Landsil Over a year ago

I'm still fighting with other issues but this resolved the question, thank you.

Collectives™ on Stack Overflow

Apps Script - read column and put in JSON and string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related