Getting data from Google sheets?

[SimplGy]

Anyone had any luck with "getting data" from Google Sheets?

Fiddled around a little bit; looks like the approaches are:

1. Apps script to extract, then something else to get that into Kit
2. the JSON feed (must be "public"?)
3. Dom scraping (but sheets isn't very dom transparent)
4. A multi-step pseudo-manual Kit workflow like: a) open the google sheet, b) copy contents of column "foo" and paste into Kit, c) repeat for column "bar", etc

[johnlindquist]

@SimplGy Here's a snippet I use in one of my scripts you can try out:

https://www.npmjs.com/package/google-spreadsheet

let { GoogleSpreadsheet } = await npm("google-spreadsheet")

let doc = new GoogleSpreadsheet("spreadsheet-id") // spreadsheet id from the URL

// read out getting an email/key here: https://www.npmjs.com/package/google-spreadsheet
let client_email = await env("GOOGLE_CLIENT_EMAIL")
let private_key = (await env("GOOGLE_PRIVATE_KEY")).replace(/\\n/g, '\n')

await doc.useServiceAccountAuth({
    client_email,
    private_key
})

await doc.loadInfo()
let sheet = doc.sheetsByIndex[0]
let rows = await sheet.getRows()
let headers = sheet.headerValues

let data = {}
for await (let h of headers) {
    data[h] = []
}

for (let row of rows) {
    for (let h of headers) {
        let value = row[h]
        if (value) {
            data[h].push(value)
        }
    }
}

[SimplGy]

Works fantastically, thank you!