Creating Google Drive Folders from Appsheet

Hello,

This question is more for those who use Google Spreadsheet as data source for Appsheet Apps and who also do some Google App Script :slight_smile:
So it’s a tricky one.
(I also left a question in the Google Sheets Community, but thought to give it a go here as well)


So whenever I start a New Project, I have to do a bunch of things that I wish to have them happen all at once by just Adding a New Project in Appsheet (what a dream).

  • I have to Copy, Paste and Rename a Google Drive “Client Template” Folder, from a “Tamplates” folder to a “Clients” folder, where all the current Clients Folders are kept.
  • I also have to add the New Project in Appsheet
  • I also have a row to link the new folder to appsheet and have that folder available from an action button in the Deck View of Projects in Appsheet.

There a few more steps, but solving these two, I can definitely solve the others.


So there are two possible ways about this

  • either have it so that when I make a new folder, the spreadsheet makes itself a new row and collects some data from that new folder (which i think is less likely to be possible)

  • either when I add a new Row to a spreadsheet, through some google script, the spreadsheet will copy the “Client Template” Folder, Paste it into the “Clients” Folder, and rename it to the value of the Cell in the Column A of the New Row that has just been added in the Spreadsheet via Appsheet (wouldn’t that be … a m a z i n g)

My main hopes are on the second version, because I can imagine that there must be an OnEdit Script in the Spreadsheet.
(if it is the first version, then the spreadsheet has to have a TimeDriven Script that would have to search every minute so it wouldn’t be instant and how would it search for changes in names of the folder … problems just keep adding up)


It can’t be done through Zapier - it can create new folder, but I need to copy, paste and rename a Template folder that contains other folders inside it!

But the fact that that option is available, makes it clear to me that this is now, a possible dream to try and achieve.
I’m sure this can be done, I’m just too much of a rookie in google script (I mean,I’ve done some things by copying bits and peaces and putting them together, but it took me two very stressful hours to realize that in a If statement in script, you have to put two equal signs instead of just one to make things work … that rookie).

So if anybody has any ideas, whether you actually have done just this, or know where to get some education on Google Script creation/organizing of Google Drive Folders from spreadsheets, or maybe a different forum/community where to ask the question … I would be truly grateful.


Of course, if I get to the bottom of this, I will leave here all the details because I can imagine how others must have tried something similar out because this Many Separate Client Template Folders is not such a unique scenario, and the need to make it easy to rename and organize information made in each folder must be a common request

Thank you and can’t wait to see this happen,
Sorin

@LeventK Probably knows the answer.

1 Like

There is an answer and somebody knows it :nerd_face:

I know this is probably a pretty complex answer so @LeventK , however you find the time for it, just guide me and I’ll happily do all the try-outs and report back with what works and what not for correction.

For starters, here is how to copy folders - https://ctrlq.org/code/19979-copy-folders-drive

To write rows to Gsheets for adding a project, I would refer to this - https://mashe.hawksey.info/2018/02/google-apps-script-patterns-writing-rows-of-data-to-google-sheets/

The third one is a bit more complicated but you can refer to this - https://developers.google.com/drive/api/v3/picker

5 Likes

Hy,

Well, that got me started alright :smiley:
I’m definitely going to solve this.


But i have a question @Bellave_Jayaram , maybe you know about this.

In Scripts in google spreadsheets, how can I make the function run onEdit or onChange, when a row is added via Appsheet?
As far as I see, adding a row through Appsheet does not fire the onEdit or onChange function, as it would if a row would be added via a Google Form.

Am I wrong, or is this true in your experience as well?

The thing is that if adding rows to the spreadsheet through filling or editing forms Appsheet is NOT recognized as an Edit or Change in the Spreadsheets Google Script, than you have to make TimeDriven Functions to check for the x last rows added that don’t have a marker and then fire on those and that’s really bad for two main reasons:

  • you can’t get instantaneous actions
  • you consume a looot of resources.

Any idea on this subject?
It would be a huuuge gain to know that you could run OnEdit functions through Google Script when filling in or editing Appsheet Forms.

Maybe the Appsheet Creators know more on this?

( I’ll write a separate question with this as well, to keep things clean on the forum. )

Yes, in my experience, onEdit and onChange are not good ways to trigger your script when working with AppSheet. I know @LeventK has worked with it and has had some success. Please see this - How to create an automated gSheet backup within your app

I prefer to trigger scripts from buttons in AppSheet and I would just have a doGet() in Code.gs that looks at the URL parameters and takes whatever action necessary.

2 Likes

@sorin_mihai
You cannot use onEdit(e) trigger with AppSheet. You can only use onChange(e). I’m using a lot with a lot of our customer apps already and never seen that it’s not working. You can check below post as well.

3 Likes

Hy,

Thanks for the thorough documentation. I’ll go through it and try to find out why onChange didn’t work for me. Hopefully, I did something wrong :smiley:

Can’t wait to make it work. I’ll be back with results.

1 Like

If you want to learn a lot about Google G Suite products and AppScript then have a look at: https://www.benlcollins.com/

There are some free guides and some more detailed paid courses.

1 Like