synthoid / exportsheetdata Goto Github PK
View Code? Open in Web Editor NEWAdd-on for Google Sheets that allows sheets to be exported as JSON or XML.
License: MIT License
Add-on for Google Sheets that allows sheets to be exported as JSON or XML.
License: MIT License
It would be better if we could save the chosen options in Sidebar
Expand the 'Replace existing file' toggle to be a dropdown with the following options:
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.
Look into allowing individual columns and/or entire sheets to have specific formatting applied to them.
For example:
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.
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?
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.
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"/>
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:
"value" : null
"value" : ""
"stringValue" : ""
"numValue" : 0
"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.
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
--}
}
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
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 ?
XML supports writing comments by using specific markup.
<!-- This is an XML comment -->
Might be nice to include comments in exported XML data somehow.
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!"
]
}
]
}
Allow columns to be ignored when exporting data. Possibly using prefixes or leaving the key cell blank.
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.
When the nested elements option is enabled the export only exports the last row in the sheet.
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" }
See if things like automated export or other Google Script interactions are possible for exported data.
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"],
]
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.
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!
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.
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>
.
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?
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"]
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" },
]
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.
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
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:
Possible solution is to export data to the root folder of the user's "My Drive" folder.
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.
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>
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.
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.
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").
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
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" : "" } ]
Allow formatting settings to be exported and loaded, either by file or copy paste into a settings field of some kind.
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.
{
"NEW01" : {
"TestRecord" : "MAJ WI NEW01",
"NumberOfInverters" : "1",
},
"NEW02" : {
... and so forth ....
{
"NEW01" : {
"TestRecord" : "MAJ WI NEW01",
"NumberOfInverters" : "1"
},
"NEW02" : {
... and so forth ....
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.
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).
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>
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.
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.
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.
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.
Add an option to include a standard XML declaration to the top of exported XML files.
<?xml version="1.0"?>
See:
MSDN
Declaration Content
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.
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>
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.