Creating schedules

data
workflow
(Sujay Karvekar) #1

Hi there,

New to Appsheet and loving it! Build 2 apps as forms for capturing info on customers. How do I scheduled nightly jobs to upload csv files to google sheets to be picked up by Appsheet and uploaded . Essentialy creating an ETL to update a table nightly/weekly/monthly so results are updated on the dashboard and lookup files.

#2

Hi @Sujay_Karvekar - I am not so clear on your use case. Am I correct that you are trying to upload some kind of CSV file to a google sheet so the latest data can be used by Appsheet? can clarify if you are uploading completely new sheets, or just refreshing data in an existing sheet?

I often am pulling in data to existing sheets that are then used to drive my apps. Unfortunately, i don’t think Appsheet can handle this directly. Instead to use google apps script to deal with refreshing my sheets from external sources hourly. This works well but you need to do a bit of code.

Maybe someone else has a better suggestion?

(Bellave Jayaram) #3

Hi @Mike, at present GAS is the best way for bulk data import into an existing Gsheet that is driving the app. I have thought of writing a web app using Firebase where users could set up CSV imports (they would specify the uploads folder, the name of the Gsheet file or its id, the sheet name, and some frequency) but haven’t yet gotten around to it.

Usually though one always wants to pre-process the CSV file in some way before adding the data in to the Gsheet and that is where I am not sure my idea is feasible. For example in one of my clients’ app, I need to strip the text in a column in the CSV before pasting it.

In your case, is it a simple case of appending the lines from the CSV to the Gsheet?

#4

@Bellave_Jayaram - agree, which is why I use GAS to pre-process bulk sheet updates.
Hopefully @Sujay_Karvekar will see our responses and can explore that direction.

btw: some good sources of help for Google Apps script are:

New GAS Community (since G+ is shutting down)*: https://groups.google.com/forum/m/#!forum/google-apps-script-community
Stackoverflow: https://stackoverflow.com/questions/tagged/google-apps-script

*wishing my favorite communities were all in one place so I didn’t have to bounce all over to stay engaged. G+ was a good central location. In my opinion, the new Appsheet Community (discourse) is a much better engagement experience than the path the GAS group took (google groups).

1 Like
(Sujay Karvekar) #5

So here is the case…

I will get a weekly data dump in a csv/ecel format from a sql server. This is updated data with customer names assigned to each sales rep , location etc. In Appsheet I want every user to see this updated data on weekly basis, every Monday morning each sales rep has updated list of customers they need to followup.

How do I get this data dump which sits on google drive to be picked up by Appsheet via a scheduling process, so I don’t have to manually create a google sheet everytime and pull it into Appsheet.

Thanks!

#6

@Sujay_Karvekar - as indicated above, I recommend you use google scripts to import the CSV to a file (let’s call it DATA) on your google drive. That can be automated with google scripts to import the CSV at whatever frequency you want (daily, weekly, etc). You can then use that sheet (DATA) as a source for your app.

A good example of how to use scripts to import the CSV is here:

> Import CSV examples using GAS

Hope that helps

1 Like