Google sheets to elasticsearch missing data in 'successful' push

[geoff_yalo (Geoffrey)]

Hi,

I'm using the Google sheets add-on "Spreadsheets to Elasticsearch" to upload a Google sheet to Elasticsearch. I'm able to "successfully" upload the spreadsheet, however, when looking in Elasticsearch the index appears without any data (0 documents).

My data looks like this:

When I click "The data is accessible here", I see the following (which obviously doesn't contain the data I attempted to upload:

Any tips?

[val (Val Crettaz)]

That add-on is quite old and hasn't gotten any updates in 2 years. A few questions to help you troubleshoot this:

• Which version of ES are you using? (It can neither be ES 6.x nor ES 5.x because the index template used by the add-on is not compatible)
• What do you get when searching the cluster directly using curl -XGET localhost:9200/your-index/_search ?
• What do you get when running curl -XGET localhost:9200/_cat/indices?v ?

[geoff_yalo (Geoffrey)]

Thanks for the quick reply @val

1. I'm using version ES 5.3.6. I imagine this is the cause of my issues (since you say this version is not compatible with the add-in)

2. This is what I get when I search the cluster directly (the index is empty):
   

3. Was not able to successfully run this - haven't had much time to troubleshoot

[val (Val Crettaz)]

Yes, indeed, the problem is that the add-on tries to install an index template that is only compatible with ES 2.x.

We might be able to bypass the template creation , though. In the Index Settings section of the add-on, use the following values:

• Index: test-index
• Index type: test-type
• Template name: test-template

Then run the following query:

curl -XPUT localhost:9200/_template/test-template -d '{
  "order": 0,
  "template": "test-index",
  "settings": {
    "index.refresh_interval": "5s",
    "index.analysis.analyzer.default.type": "standard",
    "index.number_of_replicas": "1",
    "index.number_of_shards": "1",
    "index.analysis.analyzer.default.stopwords": "_none_"
  },
  "mappings": {
    "test-type": {
      "dynamic_templates": [
        {
          "string_fields": {
            "mapping": {
              "type": "text",
              "fields": {
                "raw": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "match_mapping_type": "string",
            "match": "*"
          }
        }
      ]
    }
  },
  "aliases": {}
}'

This will properly create the template in your ES and the add-on will bypass this step. That should then allow you to push your data and play with it.

Let me know how it goes.

[geoff_yalo (Geoffrey)]

@val when I run the query, I get the below:

But I'm not sure if this is doing anything; when I push my data using the names you indicated, I still ended up with an empty index (using curl -XGET localhost:9200/test-index/_search)

[val (Val Crettaz)]

The first response you get, i.e. {"acknowledged": true} is a good sign and indicates that the template was installed successfully.

Can you reinstall the template again AND make sure to leave the "Template name" input field empty when pressing the "Push to cluster" button.
Also did you make sure to select a valid data range in your spreadsheet before pressing the button? Can you screenshot your "Send Data to Cluster" panel?

[geoff_yalo (Geoffrey)]

Hi @val,

I re-installed the template (exactly as previously, leaving in "test-template" at the start of the code. I still do not see any data in the index when pushing the table. Here is the screenshot:

[val (Val Crettaz)]

It's weird, I've even tested this against a 6.1.2 ES cluster and it worked.
What do you see below the panel when you hit the "Push to cluster" button?