synthoid/exportsheetdata

Add-on for Google Sheets that allows sheets to be exported as JSON or XML.

exportsheetdata's Issues

(JSON / XML) Time values are not formatted properly when exporting

Because of how Google Sheets stores time based data, time values are formatted strangely when exporting. Should format in a more expected and readable way.

(JSON / XML) [Unwrap] sheet tag

Add option to unwrap sheets starting with a specific prefix (maybe "UNWR_" by default) so their content is not grouped under a parent JSON object or XML element.

So (dashes added for formatting):
<data>
--<sheet1>
----<sheet1row/>
----<sheet1row/>
----<sheet1row/>
--</sheet1>
--<sheet2>
----<sheet2row/>
----<sheet2row/>
----<sheet2row/>
--</sheet2>
</data>

Would become:
<data>
--<sheet1row/>
--<sheet1row/>
--<sheet1row/>
--<sheet2row/>
--<sheet2row/>
--<sheet2row/>
</data>

Only last line is exported in JSON

Hi,

When trying to export data in JSON, only the last is actually exported.
I have tried on multiple Google Sheets with the same result.
I have left all the default parameters and I am exporting only the Current sheet.

Henri

(JSON) Add support for 2D array exports of sheets

Add support for exporting sheets as 2D arrays of row values. See below for example:

[
  ["header1", "header2"],
  ["row1_col1_value", "row1_col2_value"],
  ["row2_col1_value", "row2_col2_value"],
]

Save the settings

It would be better if we could save the chosen options in Sidebar

JSON format for MongoDB import

It would be great to get a JSON file which can easily be imported into MongoDB (at the moment I get only one document with "mongoimport" for an entire spreadsheets with multiple rows). Here is an example for a working JSON file from MongoDB: https://raw.githubusercontent.com/mongodb/docs-assets/primer-dataset/primer-dataset.json

(JSON / XML) Complex Nested Elements

Allow keys to be tagged and grouped together in a single JSON or XML element?

For example:
Keys: [Factors]Strength, [Factors]Speed, [Factors]Size
Values: 10, 30, 5

Would become:
JSON: "Factors" : [ { "Strength" : 10 }, { "Speed" : 30 }, { "Size" : 5 } ]
XML: <Factors Strength="10" Speed="30" Size="5"/>

(JSON / XML) Save / Load export settings

Allow formatting settings to be exported and loaded, either by file or copy paste into a settings field of some kind.

Invalid JSON format when enabling "Ignore empty cells" option

When exporting a JSON file with the "Ignore empty cells" option enabled, if a cell in the last row is empty, then I can get a comma just before a closing brace, which is incorrect JSON formatting and make my NodeJS process fail when I try to do a JSON.parse on it.

Here an example of the data I get (comment added by me to point to the problematic comma)

{
  "test" : [
    {
      "name" : "tacoMan",
      "text" : "hello", // <---- HERE
    },
    {
      "name" : "tacoMan",
      "text" : "Oh, is that a taco... for me?",
      "answers" : [
        "taco:yes, it is for you",
        "notaco: no way!"
      ]
    }
  ]
}

(General) Nested element implicit value caching cannot share same end ID

Nested elements currently does not support multiple nested values that share the same end key. For example: [planets]{#ID} and [meteors]{#ID} cannot exist in the same sheet currently as the way caching works only looks at the final key value (in this case "ID").

Should be able to cache values based on the path, not just the key.

(General) First Column won't export

Hi,

I'm using your add on to export a Google Sheet in XML format. I've selected the "Include First Column" option, yet when I open the XML in Microsoft Excel, the first column never appears.

Please help!

(JSON / XML) Select format for new line character

Currently, newlines are defined with the basic LF newline character "\n".

Should have a dropdown option to select between CR+LF ("\r\n") and just LF ("\n").

(XML) Support for exporting columns as inner text

Add support for exporting columns as inner text of a row element instead of attributes or child elements.

For example:

<data>
  <row>This is inner text</row>
</data>

Improper closure of last element if next columns are to be ignored.

If you have a sheet in which the last element that you want to include in the .json is followed by "ignored" columns, the export appends a comma at the last element before the closing brace. Seems to be an issue of not checking to make sure there is another active column to be included ...

Not a big deal but most of my sheets will use the far right for index columns if needed. The quick fix is to put a live column on the far right but this is not ideal.

Current:

{
"NEW01" : {
"TestRecord" : "MAJ WI NEW01",
"NumberOfInverters" : "1",
},
"NEW02" : {
... and so forth ....

Ideal:

{
"NEW01" : {
"TestRecord" : "MAJ WI NEW01",
"NumberOfInverters" : "1"
},
"NEW02" : {
... and so forth ....

(General) Allow 'selected range' export target

Allow the currently selected range of cells to be an export option along with the current options of all sheets and current sheet only.

If no range is selected when this option is chosen, the entire sheet will be exported (identical to current sheet only).

Nested json objects

Thanks for a great product!

I'm trying to create a nested json like this:
{ "Kite": { "Attach": "a", "Flower": "b", "NewObject": [ "c", "d" ] }, "Snail": { "Attach": "d", "Flower": [ "a", "b", "c" ], "NewObject": "f" } }

Is this possible already?

I was hoping I could have a fixed first column for the keys of the nested objects.

(JSON) Properly format cell JSON objects

If the option to export cells wrapped with { } as JSON objects is active, the cell's data does not get formatted before exporting. It should be automatically formatted like other JSON values, and indented in a similar manner to the rest of a JSON export.

(JSON / XML) Look into possible interactions with Google Scripts

See if things like automated export or other Google Script interactions are possible for exported data.

(JSON / XML) Allow columns to be ignored when exporting

Allow columns to be ignored when exporting data. Possibly using prefixes or leaving the key cell blank.

(General) 'Replace existing file' options

Expand the 'Replace existing file' toggle to be a dropdown with the following options:

• Don't Replace (Don't delete or replace any files)
• Trash (Trash existing files with the same name in the same folder)
• Rewrite (Similar to trash, but the most recent file with the same name will have its contents replaced with the newly exported data)

(JSON) Cell arrays export number arrays as string arrays

Exporting cell arrays populated by numbers seems to export an array of strings. For example, a cell with content:

200, 1.5

Should export as:

[200, 1.5]

But is currently exporting as:

["200", "1.5"]

Sheet title no longer exporting in XML

Previously this app would export the XML file with the following format:

<rootElement>

    <sheetTitle>

        <element>

        </element>

    </sheetTitle>

</rootElement>

Now the app skips appending the sheet title, why is this?

(XML) Allow certain rows to export as comments

XML supports writing comments by using specific markup.

<!-- This is an XML comment -->

Might be nice to include comments in exported XML data somehow.

(General) Allow empty nested elements

Allow the nested elements feature to create empty elements like an empty JSON array ( [] ), or empty XML element with open and close tags ().

This is most useful for making sure that all expected fields exist. Could simply be an extra check box for nested elements that will allow empty cells to create elements in their key path, but not populate them with an empty value. IE a column with key [traits]ID and an empty cell value, would create "traits" : [] instead of the current "traits" : [ { "ID" : "" } ]

(General) Generate sidebar with template service

Look into using the HTML service's template functionality to create the ESD sidebar. This will break up the current Sidebar.html file into a simple HTML template file, a CSS file, and a JavaScript file then assemble them together when needed. Should make editing code a little easier.

Nested Elements (JSON/XML) exports only 1 row

When the nested elements option is enabled the export only exports the last row in the sheet.

(JSON) Empty value formatting and null value support

Allow users to specify how they want exported empty values to be formatted.

Currently, empty cells are exported as "" (an empty string), but options for the following should be allowed:

1. null objects: "value" : null
2. Empty strings: "value" : ""
3. Default values:
   a. "stringValue" : ""
   b. "numValue" : 0
   c. "boolValue" : false

Default values would be nice to have, but are far more complex than other options as it would mean looking at previous and/or subsequent rows to get the expected value type. It would also need a fallback (probably null or "") in cases where there are no other values to compare to.

(General/JSON) Sidebar sheet selection doesn't work properly

Hi,

I noticed something odd recently. Everytime I deselect one of my sheets in the list and select another sheet instead, the exporter does not export the newly selected sheet. It only does that when i export the sheet again. I am guessing this has something to do with the sidebar not saving settings properly?

(General) Collapse single row sheets option

Add an option to collapse single row sheets.

Unlike unwrapping sheets, where the data from the only row in a sheet is placed into the root of the main JSON blob, this option unwraps the row's JSON so it is in the sheet's JSON content. For example:

Sheet Name: Test

Input:
Field1 | Field2 | Field3
Lupin | false | 0.75

Default Output:
{
--"Test": {
----"Lupin": {
------"Field1":"Lupin",
------"Field2":false,
------"Field3":0.75
----}
--}
}

Unwrapped Output:
{
--"Field1":"Lupin",
--"Field2":false,
--"Field3":0.75
}

Collapsed Output:
{
--"Test": {
----"Field1":"Lupin",
----"Field2":false,
----"Field3":0.75
--}
}

Convert text format to html tag

It would be great to export a formatted text like the following:

My text contains a bold word.

to:

My text contains a <b>bold</b> word or My text contains a <strong>bold</strong> word

Of course, when exporting to xml, it means that we should only propose this option when the option "Export columns as child elements" is checked to keep xml valid and add <![CDATA[]]> like this:
<![CDATA[My text contains a <b>bold</b> word]]>

Please consider this feature. Thanks for your addon.

(JSON) Wrong format when `Export contents as array` checked

It should be square brackets instead of curly brackets.

(JSON) Columns preceding columns with empty keys do not have a comma after them

Data from a column preceding a column that will be ignored when exporting do not have a comma placed after the value, resulting in incorrect formatting.

This is only apparent if the ignored column has another column after it that is exported.

(XML) Sequential complex elements

This issue will likely not be resolved until sometime after Complex Nested Elements.

Allow complex elements with specific key formatting to export as elements containing different information, but using the same name. The column key should end with "#N" (where N is a number). The reason a specific number is needed is so data does not get incorrectly placed under another element.

So (from a sheet labeled "Users"):

username | [permission#1]status | [permission#1]title | [permission#2]region | [permission#2]server
Sadao | Admin | Demon King | US | East Coast
Asura | Player | Reaper | Europe | Central

Would export as (dashes added for formatting):

<data>
--<Users>
----<user>
------<username>Sadao</username>
------<permission>
--------<status>Admin</status>
--------<title>Demon King</title>
------</permission>
------<permission>
--------<region>US</region>
--------<server>East Coast</server>
------</permission>
----</user>
----<user>
------<username>Asura</username>
------<permission>
--------<status>Player</status>
--------<title>Reaper</title>
------</permission>
------<permission>
--------<region>Europe</region>
--------<server>Central</server>
------</permission>
----</user>
--</Users>
</data>

A similar principle may be applicable to sequential sheets as well.

(JSON / XML) Select specific columns / sheets for specific formatting options

Look into allowing individual columns and/or entire sheets to have specific formatting applied to them.

For example:

• Having a column marked to export cells as JSON arrays even if they have only one element.
• Mark one sheet to export as a JSON array while all other sheets export as JSON objects.

Visualize and Export buttons throws errors

Currently logs an error in the console

Uncaught Error: Authorization is required to perform that action.

Unfortunately I'm not sure how to add more information to this bug report, but the add-on does not currently work at all.

3 XML level nested

How do I have to design the sheet and what settings do I have to choose in the sidebar, in order to get a 3 level nested xml output, like:

<root>
 <fruit>
  <description>
   <en>Some text</en>
   <de>etwas text</de>
   <es>poco texto</es>
  </description>
 </fruit>
</root>

I only seem to achive this if I include the last level as text in the field content, using a sheet like:

root   description          description           description  
fruit  <en>Some text</en>   <de>etwas text</de>   <es>poco texto</es>

(JSON) Allow sheets to export as arrays of values

Allow sheets containing one column to export as an array of values, instead of an array of objects.

A sheet named "Formatting" with a single column with the key "Words" and the following values:

Until
When
While

Would export as: (dashes added for formatting)

"Formatting" : [
--"Until",
--"When",
--"While"
]

Instead of:

"Formatting" : [
--{ "Words" : "Until" },
--{ "Words" : "When" },
--{ "Words" : "While" },
]

(JSON / XML) Allow customization of separator chars when formatting data

Look into allowing chars used to separate values (' , ' for separating array elements for example) to be changed by the user.

Possibly default to the comma ( , ) char but can be adjusted in the sidebar.

[JSON] Only exports distinctive columns

I noticed something critical, that wasn't there a few months ago. When i try to export a sheet where every column has the same content in the first line, it only exports one of those columns.

Stopped working after update

Stopped working after upgrade. Just spins and doesn't bring up JSON. I am doing it for the current sheet and all other fields are the default.

Console error: Uncaught Error: We're sorry, a server error occurred. Please wait a bit and try again.(…)

Have been getting this since yesterday.

(General) Write Documentation

Write documentation pages for each of ESD's options. Ideally each page should be as thorough as possible and use images and examples when needed.

Multiple users working on same document seem to break

save settings toggles seem to get out of sync if 2 users change the settings (so for instance a custom sheet now can only be added to the export if you UNCHECK its radio-button) and the Replace existing file(s) button if selected breaks the export if 2 different users try and export the same document - it just spins indefinitely. unchecking the box allows export

(General) Select location of exported data

Allow users to select the folder for exported data to be created in. The default behavior will always assume the same folder as the original sheet, but being able to export to a specific folder would help with things like automation.

(XML) Allow to not insert tag related to the sheet in multiple sheet export

Should be very useful have the possibility to chose to hide the "sheet tag" in the multi sheet export, like in this example....

<?xml version="1.0" encoding="UTF-8"?>
<data>
  <sheet1>
    <sheet1-row attribute1.1="content 1.1.1" ... attribute1.M="content 1.M.1"/>
    <sheet1-row attribute1.1="content 1.1.2" ... attribute1.M="content 1.M.2"/>
    <sheet1-row attribute1.1="content 1.1.3" ... attribute1.M="content 1.M.3"/>
  </sheet1>
  <sheet2>
    <sheet2-row attribute2.1="content 1.2.1" ... attribute2.N="content 2.N.1"/>
    <sheet2-row attribute2.1="content 1.2.2" ... attribute2.N="content 2.N.2"/>
    <sheet2-row attribute2.1="content 1.2.3" ... attribute2.N="content 2.N.3"/>
  </sheet2>
<!-- .... -->
<sheetZ>
    <sheetZ-row attributeZ.1="content Z.2.1" ... attributeZ.T="content Z.T.1"/>
    <sheetZ-row attributeZ.1="content Z.2.2" ... attributeZ.T="content Z.T.2"/>
    <sheetZ-row attributeZ.1="content Z.2.3" ... attributeZ.T="content Z.T.3"/>
  </sheetZ>
</data>

Hiding , , ... , tags...

<?xml version="1.0" encoding="UTF-8"?>
<data>
    <sheet1-row attribute1.1="content 1.1.1" ... attribute1.M="content 1.M.1"/>
    <sheet1-row attribute1.1="content 1.1.2" ... attribute1.M="content 1.M.2"/>
    <sheet1-row attribute1.1="content 1.1.3" ... attribute1.M="content 1.M.3"/>
    <sheet2-row attribute2.1="content 1.2.1" ... attribute2.N="content 2.N.1"/>
    <sheet2-row attribute2.1="content 1.2.2" ... attribute2.N="content 2.N.2"/>
    <sheet2-row attribute2.1="content 1.2.3" ... attribute2.N="content 2.N.3"/>
<!-- .... -->
    <sheetZ-row attributeZ.1="content Z.2.1" ... attributeZ.T="content Z.T.1"/>
    <sheetZ-row attributeZ.1="content Z.2.2" ... attributeZ.T="content Z.T.2"/>
    <sheetZ-row attributeZ.1="content Z.2.3" ... attributeZ.T="content Z.T.3"/>
</data>

There are some import tools that process the row of different entity type, as a simple list of "one level tag", as in the second example.

Another option should be create the sheetN-row tag name (, , ...), not with the first column of the table, but with the name of the sheet.

Thanks for the wonderful effort on this project.

(Exporting) Sheet permissions interfering with export

Some sheets may not export due to permission issues with the sheet's parent folder on Drive. Owners or other uses with read/write permissions for a folder should be able to export as expected, but others will get stuck at the exporting stage.

Possible scenarios for this include:

• The sheet, but not its parent folder is shared with a user
• User does not have permission to modify the sheet or its parent folder

Possible solution is to export data to the root folder of the user's "My Drive" folder.

(JSON / XML) Option for not exporting cells with empty or null values

Add an option for not exporting individual cells from a row as either XML attributes / elements or JSON values if the cell is blank.

This should help cut down on file size by not including elements for empty cells, at the cost of the user end application checking that a value exists first.

Example:
{ "name" : "Gorloc", "age" : 20, "birthplace" : "", "occupation" : "Barbarian" }

Would become:

{ "name" : "Gorloc", "age" : 20, "occupation" : "Barbarian" }

(XML) Option to include XML Declaration

Add an option to include a standard XML declaration to the top of exported XML files.

<?xml version="1.0"?>

See:
MSDN
Declaration Content

(General) Save your last export settings

When you use ExportSheetData every day on various document, you end up applying always the same settings several time in a day. If the sidebar could memorize your last settings, it would be much easier to keep the same export formatting every time.

In fact, it works as long as you stay on the same document. But as soon as you open a new spreadsheet, your sidebar come back to the default settings.

Something store in your google drive settings ?

Generates invalid XML names

As far as I can see the tool make no attempt to ensure that the XML element names generated are valid names. In fact, it goes about it completely the wrong way: it formats names using a routine formatXmlString which converts < to <, etc, despite the fact that entity references are not allowed in names. I'm seeing XML in which the element names are numeric (<1>) or contain spaces (<birth date>.

Tag non-exportable columns

We should be able to tag the columns we don't want to export. For exemple we can add the string [NOEXPORT] in the column title.