Documentation

«ß-XML»: The name of the project

«dataSheet»: The spreadsheet where the user organizes data for the main set of XML elements

«linkedSheet»: The spreadsheet where the user organizes data for the set of XML elements extending the main set of XML elements

«anchorString»: A string used to identify a «specialCell» that the script will search for in order to find the cell address of the latter

«specialCell»: A cell designated to store information required for specific purposes, if desired by the user

«userConstant»: A constant variable that can replace or displace its corresponding «specialCell», if desired by the user

«optionalArea»: The initial block of rows in «dataSheet»s, where «specialCell»s must be located

«header»: The second block of rows in «dataSheet»s and the first block of rows in «linkedSheet»s, where the user defines the XML format and can exclude particular columns from the XML file

«dataset»: The third block of rows in «dataSheet»s and the second block of rows in «linkedSheet»s, where the user structures the data

IMPORTANT: This documentation provides instructions for transferring data from spreadsheets to XML files. For practical reasons, users are expected to understand and be aware of the process for reversing this transfer, provided they have carefully studied this documentation.

To begin, the user should download the Microsoft Excel or LibreOffice Calc document, or clone the Google Sheets document from the links available on the Home page.

The documents contain the «ß-XML» script, a sample «dataSheet», and a sample «linkedSheet».

Every sheet must contain two required blocks of rows, as can be found in the sample sheets. In addition, «dataSheet»s may optionally include an additional block to help the script locate the «specialCell»s and read their contents:

1. 1. 1. 1. The «optional area»

         2. The «header»

         3. The «dataset»

Once the user has the document open, they

• • • • either create their own sheets after clearing the sample sheets

      • or, alternatively, copy the embedded script into their document,

      • then start to populate the document with XML elements and data as outlined in this documentation,

      • and ultimately run the script to save the XML file from the active sheet to their drive.

When initiated,

1. the script processes both the active «dataSheet» and the «linkedSheet» associated with it by the user.

This association occurs when the desired XML standard includes an additional set of elements intended to extend the main XML elements, which the user may want to utilize. For instance, in the KML standard, the additional XML elements include «Style» elements that extend the main elements. In such cases, the data and styles need to be organized in their respective sheets: the «dataSheet» and the «linkedSheet».

2. the execution scope of the script on sheets extends,

• • • • horizontally, from the first column of the sheet to the last column of the «header»,

      • and, vertically, from the first row of the «header» to the last row of the «dataset»,

      • with the exception of the rows between a blank row inserted by the user within the «header» and the «dataset».

      • Additionally, the range of execution includes the «specialCell»s found in the «optionalArea» of the active «dataSheet».

      • The script does not operate beyond these defined boundaries.

      • Because the horizontal range is determined by the last column of the «header», all the cells in the next column, where the last XML element is defined, must be blank. However, this rule does not apply to the horizontal range of the «dataset»s and the user can use the cells starting directly with the next column of a «header» for other purposes than just entering data.

      • Similarly, because the vertical range is determined by the last row of the «dataset», all the cells in the next row, where the last "datarow" is entered, must be blank. However, as mentioned above, the user can keep a blank row after the last XML element in the «header» and use the rows between it and the «dataset» for other purposes than just defining XML elements.

Readers will gain a clear understanding of how the script works by reviewing "Section 3", "Section 4", and "Section 5". This knowledge will be further reinforced in "Section 6" through illustrations that clarify the concepts explained in the previous sections. Users who prefer to take on challenges independently can skip straight to "Section 6".

Details regarding the desired XML file and script execution, as well as optional specifics related to KML files

• 1. can be stored in cells referred to in this documentation as «specialCell»s

  2. alternatively, can be assigned to constant variables referred to in this documentation as «userConstant»s.

The area of a sheet where «specialCell»s are located by the user is referred to in this documentation as «optionalArea». «OptionalArea»s can exist only in «dataSheet»s.

The «userConstant»s can be found in the "Export" and "Import" modules of the script. The user can assign different constant values ​​for each module.

The user can choose to include any, some, or no «specialCell» while assigning all the other information to «userConstant»s.

• • • • If the user includes a «specialCell» and also assigns a value to its corresponding «userConstant», the content of the former will take precedence over the value of the latter.

      • If the user includes no «specialCell», there is no need to include the «optionalArea», either, because the «optionalArea» is required only to help the script locate the «specialCell»s.

The user can also include cells with values or calculations in «optionalArea»s for added convenience. If they prefer to avoid adding «optionalArea»s to maintain cleaner «dataSheet»s, they can place such cells outside the script's execution scope, as explained in "Section 2".

IMPORTANT: When the script identifies a «specialCell» within the first 255 rows, it classifies the sheet as a «dataSheet» with an «optionalArea». In this scenario, the «header» can, by adding a top border, begin in a row after the last identified «specialCell». However, if no «specialCell» is present, the «header» may start from the first row of the sheet. In this case, a top border is not required; nevertheless, the user has the option to designate a row other than the first as the beginning of the «header» by adding a top border so that they can use the rows above the «header» for calculations or for values that will be used in calculations.

1. «SpecialCell»s:

«SpecialCell»s are specific cells that, as previously mentioned, are associated with corresponding «anchorString»s. These «anchorString»s help the script identify which cell the user has entered data into, and they also serve as labels for the users. Below is the list of «anchorString»s or "labels" for «specialCell»s, each explained at the end of this section:

• • • • «The XML»

      • «File path and name»

      • «Linked sheet»

      • * «Document»

      • * «Document LookAt/Camera»

      • * «Document ScreenOverlay»

      • «Application»

      • «Last activity»

*: Information specific to KML files extending the root element. KML creators must review the related headings below. If processing similar information for other XML files is necessary, this should be incorporated into the script if deemed valuable.

As can be observed in the sample sheet, each «specialCell» can

• • • • either be a single cell containing an annotation that matches the corresponding «anchorString»,

      • or a cell located one row below the cell that contains the corresponding «anchorString», followed by a colon character.

All «specialCell»s must be located within the «optionalArea»s.

Users can modify the «anchorString»s by changing the values of the relevant constants within the "Export" and "Import" modules of the script.

«OptionalArea»s

• • • • • • • may also include cells that contain user-required values and calculations,

            • must be included in «dataSheet»s but never in «linkedSheet»s,

            • can consist of a maximum of 255 rows,

            • begin with the first row,

            • conclude where the «header» begins, which is marked by a top border (see "Section 4" below), and

            • begin, horizontally, with the first column and extend to the rightmost column in the sheet.

2. «UserConstant»s:

This option helps create cleaner «dataSheet»s by reducing or eliminating the presence of «optionalArea»s.

Users can locate the constants in the "Export" and "Import" modules of the script and assign values to them:

• «cUserConst_TheXml»: The equivalent of the «specialCell» anchored with the string "The XML"

• «cUserConst_FilPth»: The equivalent of the «specialCell» anchored with the string "File path and name"

• «cUserConst_LnkdSh»: The equivalent of the «specialCell» anchored with the string "Linked sheet"

• * «cUserConst_Documt»: The equivalent of the «specialCell» anchored with the string "Document"

• * «cUserConst_LooCam»: The equivalent of the «specialCell» anchored with the string "Document LookAt/Camera"

• * «cUserConst_ScreOv»: The equivalent of the «specialCell» anchored with the string "Document ScreenOverlay"

• «cUserConst_Aplctn»: The equivalent of the «specialCell» anchored with the string "Application"

• The  «specialCell» «Last activity» does not have a corresponding «userConstant» since it is the script that sets the value, not the user.

*: Information specific to KML files extending the root element. KML creators must review the related headings below. If processing similar information for other XML files is necessary, this should be incorporated into the script if deemed valuable.

As explained above, if a value is assigned to a «userConstant», the corresponding «specialCell» is no longer required. If the latter exists anyway, the «userConstant» becomes redundant. Therefore, it is advisable to assign information that may be shared across most of the sheets in the document to «userConstant»s, while adding the «specialCell»s with unique information to individual sheets.

However, it is recommended to stay vigilant regarding the following situation:

If a «dataSheet» does not contain any «specialCell», the script will load and share the values of the «userConstant»s, regardless of whether they are blank, across all the «dataSheet»s.

In this scenario, a key point to note is that the information shared across all the sheets will include the file path and names of the XML files created from the individual sheets in the document. To resolve this issue, users have two options:

• • • • • • they can leave the value blank, so as to have the script prompt the "Save As" dialog every time it is run,

          • or they can take note of the time when the script execution finishes in order to find the correct file in the folder (As a precaution, the script does not overwrite files with same names. Instead, it appends the date and time information to the file name if a file with a same name already exists in the folder.).

While assigning values to «userConstant»s, character #10 can be used as line feeds.

IMPORTANT: Users must keep in mind that straight double-quote characters must be written as double double-quote characters in variable values within the script modules.

-----   u   -----   u   -----   u   -----

Details regarding assigning values/information to «specialCell»s and «userConstant»s:

«SpecialCell»: «File path and name» / «UserConstant»: «cUserConst_FilPth» / (optional)

This is information about the path and file name for the XML file that will be created. If the file name does not end with the file extension of the XML standard, the script will automatically add it.

The filename can be entered by the user or, in certain cases, filled in, corrected, or edited by the script after the "Save As" dialog concludes.

The script can handle cases when documents are moved between operating systems (Windows, macOS, or Linux).

The script can, in addition, handle four scenarios for user preferences regarding typing paths and file names:

1. Bare directory and file name entries:

• • • • If the user provides an absolute path and file name, the file will be saved directly in the specified directory without prompting the "Save As" dialog.

      • Conversely, if the user provides only an absolute path, the script will prompt the user with the "Save As" dialog to enter the file name.

      • If, on the other, the user provides only a file name, the file will be saved in the directory where the spreadsheet is saved.

      • If the path is a relative path, the folders will be created and the file will be saved in the directory where the spreadsheet is saved.

2. Shortcuts for preferred directory:

• • • • To save the file in the root folder of the operating system drive, the information should start with a colon character. 

      • To save the file in the user folder of the operating system, the information should begin with the path delimiter. 

3. The path delimiter:

• • • • They can choose their preferred path delimiter, including backslash (\), forward-slash (/), greater-than (>), and less-than characters (<), regardless of the current operating system.

      • If the delimiter is the less-than character (<), the path and file name are expected to be in reverse order.

      • When the script encounters more than one of the four specified characters in the information, the following priority is applied for determining the delimiter:

        • • • If the first character is a slash (/) or a greater-than (>) character, the delimiter will be set to that character.

            • If the last character is a less-than (<) character, the delimiter will be set to less-than character.

            • If the information contains a backslash (\), the delimiter will be set to the backslash character.

            • If the information contains a forward-slash (/), the delimiter will be set to the forward-slash character.

            • If the information contains a greater-than (>), the delimiter will be set to the greater-than character.

            • If the information contains a less-than (<), the delimiter will be set to the less-than character.

            • If none of the above conditions are met, the delimiter will default to the standard delimiter of the operating system (OS).

      • Once the delimiter is set, all its alternatives and any illegal naming characters—such as colons, vertical bars, double quotes, question marks, asterisks, and carets—in folder and file names will be encoded by the script.

4. If a folder in the specified path does not exist on the current drive, the script will take action based on the following priorities:

• If the root directory is compatible with the operating system, the file will be saved in the exact directory path specified.

• If not, the file will be saved directly in the document's folder. Situations where the drive is currently inaccessible can also be included in this category. This highlights the importance of considering all potential access issues.

• Another possibility is that the user created a new document and copied the script into it but has not yet saved the document. In this case, the script will reference the user folder of the operating system rather than the folder of the document.

IIMPORTANT: Users must keep in mind that although the script encodes invalid characters, the "Save As" dialog, depending on the application and its version, may not, when prompted, correctly point to the path with names containing such encoded characters.

MPORTANT: Users should be aware that the information regarding the operating system, and so on may not apply to Google Sheets. It's important to understand the main logic of the script in order to grasp how it works within a Google Sheets document.

«SpecialCell»: «Linked sheet» / «UserConstant»: «cUserConst_LnkdSh» / (optional)

This information identifies the name of the «linkedSheet» that will extend the XML elements erected in the «dataSheet» that the user wants to associate.

«SpecialCell»: «The XML» / «UserConstant»: «cUserConst_TheXml»» / (required)

This information provides the basics of the XML standard, organized by line breaks:

• • • • The file extension associated with the XML standard. (assumed to be a TXT file if it is blank or if the «specialCell»/«userConstant» is blank)

      • (optional) The label for the XML file format. (required for the "Save As" dialog)

      • The XML declaration line of the XML standard.*

      • The root node of the XML standard.*

      • (optional) The ID of the root node of the XML standard.*

*: These components may be required by the XML standard; however, if they are blank or the corresponding row does not exist, the script will silently ignore adding them to the XML file.

«SpecialCell»: *«Document» / «UserConstant»: «cUserConst_Documt» / (optional)

This optional information for KML files extends the root element.

For XML files other than KML and for KML files that will not include the particular information, the «specialCell» may be removed or both the «specialCell» and its corresponding «userConstant» may be kept blank.

Separated by line breaks, it is to contain the following optional values, respectively:

• The name of the Document

• The snippet information. It should begin with a numeric or blank value, indicating the maximum lines, followed by a caret and the snippet text itself.

• The description for the Document

• The style URL

• The time information. It can be represented in two ways: as a single <TimeStamp> or as a <TimeSpan> defined by two values separated by a caret (^) symbol. If one of the values in the <TimeSpan> is left blank, it indicates an open-ended timeline.

Example:

This is the name of the document

^I don't want a visible snippet

<![CDATA[<b>My</b> town]]>

#m_doc

1970^1975

«SpecialCell»: *«Document LookAt/Camera» / «UserConstant»: «cUserConst_LooCam» / (optional)

This optional information for KML files extends the root element.

For XML files other than KML and for KML files that will not include the particular information, the «specialCell» may be removed or both the «specialCell» and its corresponding «userConstant» may be kept blank.

When it starts with an asterisk character, the script generates a <Camera> element, instead of a <LookAt> element.

Separated by line breaks, it is to contain the following optional values, respectively:

• The <LookAt> or the <Camera> ID

• <Longitude>, <Latitude> and <Altitude> values separated by carets

• <Heading>, <Tilt> and <Range> or <Roll> values separated by carets

• The value for <altitudeMode>

• Options for <ViewerOptions> separated by carets

• The time information. It can be represented in two ways: as a single <TimeStamp> or as a <TimeSpan> defined by two values separated by a caret (^) symbol. If one of the values in the <TimeSpan> is left blank, it indicates an open-ended timeline.

Example:

*id_camera

28.975254^41.023273^0

13^0^0

RelativeToSeaFloor

historicalimagery^streetview

1970^1975

«SpecialCell»: *«Document ScreenOverlay» / «UserConstant»: «cUserConst_ScreOv» / (optional)

This optional information for KML files extends the root element.

For XML files other than KML and for KML files that will not include the particular information, the «specialCell» may be removed or both the «specialCell» and its corresponding «userConstant» may be kept blank.

Separated by line breaks, it is to contain the following optional values, respectively:

• The <ScreenOverlay> ID

• The name of the <ScreenOverlay> element

• The snippet information. It should begin with a numeric or blank value, indicating the maximum lines, followed by a caret and the snippet text itself.

• The description of the <ScreenOverlay>

• The URL of the image file

• The values for the attributes x, y, xunits, and yunits of overlayXY, separated by carets

• The values for the attributes x, y, xunits, and yunits of screenXY, separated by carets

• The value for rotation

• The values for the attributes x, y, xunits, and yunits of size, separated by carets

• The time information. It can be represented in two ways: as a single <TimeStamp> or as a <TimeSpan> defined by two values separated by a caret (^) symbol. If one of the values in the <TimeSpan> is left blank, it indicates an open-ended timeline.

Example:

id_screenoverlay

This is the name of the ScreenOverlay

1^I want the snipped limited to one line

This screen overlay uses fractional positioning to put the image in the exact center of the screen

http://earth.google.com/images/art.gif

0^0^fraction^fraction

0^0^fraction^fraction

0

0^0^pixels^pixels

2000^2025

ScreenOverlays can also be populated in «dataset»s.

«SpecialCell»: «Application» / «UserConstant»: «cUserConst_Aplctn» / (optional)

This information pertains to the path and name of the application that will launch the XML file after it is saved.

If the script detects a blank value, the file will not be launched.

If the user wants to launch the file with multiple applications, the paths and names of the applications should be separated by line breaks.

For applications preceded by asterisks, the script will not prompt the user to confirm if they want to launch the application..

«SpecialCell»: «Last activity» / (optional)

This cell is designated for the summary of the execution, and it does not have a corresponding «userConstant» because the script itself will populate the value instead of the user.

If the content of this «specialCell» is blank or it is not included in the «optionalArea», or if the «optionalArea» does not exist, the script will not attempt to assign the summary anywhere.

Check out "Section 6" for visual explanations.

The «ß-XML» script requires specific information regarding how each data column corresponds to XML elements. It also needs clarification on which data columns should be excluded from the XML file and how to structure the parent-child hierarchies. Therefore, a «header» section is essential, as it allows the user to provide all of this information.

Additionally, as explained in "Section 2", «header»s identify the horizontal execution scope of the script.

In short, a block containing

1. a single row (the first row of the «header») where the user is expected to fill in the cells of the columns they wish to include in the XML file while leaving blank otherwise, and

2. a series of rows following the first row where the user is expected to define the XML elements (attributes and tags)

is referred to in this documentation as «header».

A «header»

• must consist of a minimum of 2 rows and can contain up to 256 rows,

• must begin,

  • • 1. (in «dataSheet»s with «optional area»s) immediately after the «optional area», separated by a top border on its first row (placing a top border only on cell A is also acceptable),

      2. (in «dataSheet»s without «optional area»s) with the first row of the sheet,

      3. (in «linkedSheet»s) with the first row of the sheet,

• vertically, concludes where the «dataset» begins, indicated by a top border (see "Section 5" below), and

• horizontally, extends from the first column of the sheet to the last column that contains any values in its cells.

The user can keep a blank row within the «header» after the lower-most XML element and use the rows between it and the first row of the «dataset» for values, calculations, comments, annotations, labels, or anything they wish, provided that the total number of the «header» rows does not exceed 256.

How the script locates the «header» in a sheet? Putting it in the other way than the explanation above:

• Vertically:

  • • • In «dataSheet»s:

        • • • If the sheet includes «optional area», the range will be between the first cell in column A with a top border (inclusive) and the second cell in column A with a top border (exclusive).

            • If the sheet does not include «optional area», the range will be between the first cell of the sheet (inclusive) and the first cell in column A with a top border (exclusive).

      • In «linkedSheet»s: The range will be between the first cell of the sheet (inclusive) and the first cell in column A with a top border (exclusive), just like in «dataSheet»s without «optional areas».

• Horizontally, the range is defined between the first column of the sheet and the last column where the vertical «header» range has any values in its cells.

ATTENTION: Excluding a column may also result in the exclusion of part of the data, as the script consistently ends with rows in the «dataset» block, where all cells are blank. Users must ensure the columns they are excluding do not constitute the only non-blank cells in the «dataset».

Example:

In a «dataSheet», the user adds top borders to cells A5 and A9. Between row 5 and row 8, the first range of cells with all blank values is column K. Then, the «header» of the sheet is the range A5:J8.

The script will read nothing in the «header» starting from column L and in the «dataset» starting from column K.

A5:J5: The script will also skip the columns with blank cells in row 5. In other words, for columns that the user wants to include in the XML file, the corresponding cells in row 5 must not be blank. This means that the user can both

• utilize the columns with blank cells in row 5 for calculations (just like the columns beyond column K) and

• temporarily exclude some XML elements from the XML file for testing purposes.

A6:J8: From row 6 to row 8, the user defines the XML elements and the XML structure. This area can also be used to add headings for calculation columns.

Defining XML elements and the XML structure:

XML follows a parent-attributes-children structure. The script requires users to format it in the «header», with each parent listed in higher rows or cell lines than their attributes or children.

The exception is the <Folder> element used in KML files. Due to its unique function, a "Folder" entry, although a nestable parent, is treated differently, and the way the folders are formed in the sheet will vary. (see "Section 5" below)

The basics:

1. 1. 1. 1. Any string following an asterisk character will be considered an attribute, and a tag if not.

         2. Unpaired elements must come after a forward-slash.

The formation:

Attributes should be listed immediately below the element they belong to.

1. The simplest formation:

The simple way to form the hierarchy is

• to list the attributes vertically in cells, starting from the cell directly below the main element,

• to list the children horizontally in cells, beginning from the lower right cell to the main element and

• to ensure that siblings are aligned in the same row.

Example: An element in F6 has two attributes and three children. Then, the first attribute is to be in F7, the second attribute in F8, the first child in G7, the second child in H7, and the third child in I7.

2. Listing siblings vertically:

Alternatively, some or all siblings can be listed vertically. This format is useful when the user wants to enter the values for multiple siblings into a single «dataset» cell, resulting in a narrower and cleaner sheet.

In this vertical arrangement, siblings are lined up one below the other. At any point, the list can break and continue in the next column, allowing for flexibility in the layout. This process can be repeated as needed.

Example: Again, an element in F6 has two attributes and three children. Then, the first attribute is to be in F7, and the second attribute in F8. Then, unlike the previous example, there are different ways to arrange the children:

• The third child can be placed below the second child in H8 rather than in I7.

• Alternatively, the second child can be placed below the first child in G8, with the third child in H7.

• Another option is to line all the children up in a single column, occupying G7, G8, and G9.

What will the next column be used for in this case if it does not refer to a sibling? According to the vertical-alignment rule;

• if the first non-blank cell is in row 5 or above, then the entry in F6 ends, and a new grandparent or the sibling of a grand- or grandgrandparent begins,

• if the first non-blank cell is in row 6, then the sibling of the parent in F6 begins,

• if the first non-blank cell is in row 7, then that entry becomes the first child of the last child of the element in F6.

3. Merging vertically adjacent cells into a single cell:

Things can become complicated, but there is a solution to simplify vertical line-ups. Any number of adjacent vertical cells can be merged into a single cell, separated by line feeds.

Example: Again, an element in F6 has two attributes and three children, and the user wants to merge any or all of them.

• The parent, its attributes, and all of its children can be merged in F6, each separated by line breaks.

• The parent, along with some or all of its attributes, or the parent and all its attributes, along with some of its children, can be merged in F6, any or all in F7, the rest in F8, and so on. The remaining cells that the user does not want to merge can still be lined up in a way that aligns with the requirements outlined above.

Check out "Section 6" for visual explanations.

-----------

¹ A feature designated to allow users freely add calculation columns anywhere in «dataset»s.

A block containing the data is referred to in this documentation as «dataset».

A «dataset»

• must consist of a minimum of 1 row and can contain up to 4,294,967,296 rows,

• must begin immediately after the «header», separated by a top border on its first row (placing a top border only on cell A is also acceptable),

• horizontally, extends as far as the «header» does, and

• vertically, concludes, in the row where all the cells, within the range of the «header», in the next row are blank.

• All columns to the right of the last «header» column will be excluded from the script in the horizontal «dataset» range.

The data is to be organized as records per row, with each value/values being entered in respective cells in accordance with the layout of the «header». As explained in "Section 4" above, because the "Folder" element is a special XML element, its definition and erection are different than the other elements.

The formation of "Folder"s:

xx