Many users of WebWidgets find it useful to upload CSV / TSV files to Google Docs. These are simple tabular data sets, where each record is representing by a separate line, and the columns within the row are separated by tabs or columns. This is similar, but not identical, to the Google Export. The difference is that the Export is intended to send the exact contents of a Widget table to Google, for subsequent analysis, graphing, or just backup. In contrast, the TSV upload is intended to allow external viewing of a dataset or report that has been generated by the Widget code.

For example, suppose you have a Widget that represents all your sales, so it has one million records. These records are linked to product listings, also in the Widget. You want to generate a report that breaks down the sales by product and location, and make this data viewable by external users (i.e. people who do not have user logins to the Widget). To achieve this, you write some Widgets code to generate the report from your data. You format the output as described below, and save it to a special table. Then the system uploads it to a Google Sheet for you. You can then simply send a link to the sheet to external viewers. To do this with the Export feature would require you to send several tables to Google (sales, products, locations) and then write complex spreadsheet code to produce the report.

To use the Google Sheet CSV Upload, you must create a table in your one of your Widget DBs with the following structure:

CREATE TABLE gdoc_tsv_upload (id int, sheet_id varchar(20), tab_name varchar(10), 
 options varchar(20), header_row text,  tab_separated_data text, need_upload smallint 
uploaded_on_utc varchar(16), primary key(id));

The meaning of these fields are as follows:

• sheet_id : the Google Sheet ID of the sheet you wish to upload to. This WebWidget Google Sheet robot must have write access to this sheet.
• tab_name : the name of the tab within the sheet to upload the data to.
• options : this is a simple JSON structure that options about how the data is formatted, see below
• header_row : the header row columns for the sheet
• tab_separated_data : the actual data of the upload, represented as a large string
• need_upload : a 0/1 flag indicating if the data needs to be uploaded. You should set this to 1 when you change the data in the upload. The system will reset it to 0 when the upload runs.
• uploaded_on_utc : timestamp provided by the system when the upload runs

The uploader daemon is running continuously on the WWIO server. For all the tables with the given name (gdoc_tsv_upload) in the system, it periodically scans them to find records that need to be uploaded. When such a record is found, it sends the result to Google. Note that the data will not be uploaded immediately: you will need to wait until the next scheduled run of the uploader daemon (we are considering a feature that would allow Widget owners to request immediate upload).

You can create the target for the Google Sheet upload in any space that you own. You must grant write permissions to the WWIO robot: widgetrobot@widget-quickstart.iam.gserviceaccount.com

The options column contains a JSON structure that provides optional parameters to control the format of the header data and the TSV data. The parameters are column_delim and line_delim. These simply indicate the delimeters that are used to glue together the data into strings. Here is an example showing how to use these options properly:

        const headers = ["Employee", "Salary", "Office", "Title"];

        // This is a list of strings representing separate records
        const records = W.getItemList("employee_info")
                            .map(item => [item.getName(), item.getSalary(), item.getOffice(), item.getTitle()])
                            .join(COLUMN_DELIM);

        const payload = {
            "sheet_id" : EMPLOYEE_SHEET_ID,
            "header_row" : headers.join(COLUMN_DELIM),
            "tab_separated_data" : records.join(LINE_DELIM),
            "options" : JSON.stringify({ "column_delim" : COLUMN_DELIM, "line_delim" : LINE_DELIM }),
            "need_upload" : 1
            "uploaded_on_utc" : ""
        }

        const widgetitem = W.buildItem("gdoc_tsv_upload");
        widgetitem.syncItem();

If you do not specify the line and column delimeters, the default values will be the standard new line character \n and the tab character \t. It can be important to use special line delimeters if your source data is text that itself contains newlines.