Managing My Data: Apps Script vs Sheets Functions

0
votes

The Project: Sorting and automating donor forms. Each day I receive a spreadsheet file via email with every new donor for that day. The first step of my project is to grab the data from [ Daily Exported Data ], reformat/grab the information I need for yearly, and append it to the bottom of [ Yearly Data].

Example: [Mrs.][Jane][Doe][$30] from Daily Exported Data is pushed into Yearly Data as....

[Mrs. Jane Does][$30]

The Problem: I am new to working with spreadsheets. I have logged about 20 hours learning about google sheets vs excel, learning how to use it and brushing up on my Javascript. I was excited to be able to use/improve my javascript skills, however, it seems that if had the massive array of built in functions memorized a great deal can be done without Apps Script. My questions is therefore multifaceted.

Firstly, is it bad practice to write custom scripts when built in functions would suffice? The documentations seems thorough but not very explanatory and sifting through it to figure out how to do it "Their way" is pretty frustrating and time consuming when I could just write some JS to perform the operation myself, learn more, and have more fun doing it.

Secondly, my current plan is to just have a script that launches from the Yearly Data via a [Import Data] button. That script will then retrieve the Import Data via SpreadsheetApp.openById, get the values, make them dance about as I push their arrays around and create the newly formatted arrays for the Yearly Data. Then I just write those new arrays to the bottom of yearly data. Is this approach valid and/or preferable?

I know (and am really annoyed) that you cannot call built in functions from a custom script. So if I were to do this without a custom script Im not sure how I would do it. There is probably a =getrange(nestamillionprebuiltfunctions) that I could call from the Yearly Data sheet but...

1) Im not sure how to make prebuilt functions trigger from a button. Nor do I know how I would pull data from a different sheet via prebuilt functions and append that data to the end each time said button was clicked, as my understanding of ARRAYFUNCTION tells me it would blow up the moment I took the data that it was pulling from away.

2) I dont know how anyone can think about a complex operation on data typing with no indentation in the sheets fields, it seems that at a certain point that's not what you are supposed to do.

I have many questions I am struggling with regarding this script but, in summary, Im looking for feedback on my general approach before I become to committed :P

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

It sounds like you have initiative to do the task you are describing using Google Apps Script and looking for reassurance. GAS seems the most logical to me, based on my experience and interpretation of your task and your stated preference.

You are describing tasks such as moving data elements from Spreadsheet A to B and/or from Col C to D, ect, with some joining of the values to create new combinations in single cells. This can easily be accomplished by using GAS scripts:

  • SpreadsheetApp.getActiveSheet() or .openById(id) methods

  • Sheet and Range class methods such as .getSheets()[0] and .getRange(row, column, numRows, numColumns)

  • .getValue() or .getValues(), and then work with your numbers/strings in script.

  • .setValue(value) to put them where you want them.

Although something can be said for being comfortable with what you know, it sounds like you don't have a clear vision or background for sheets built-in functions to begin with and you'd prefer to script anyway. Using nested spreadsheet functions in the function bar sounds like a real pain to me, and ultimately limiting if you are trying to do fancy stuff like buttons, or automated import of csv ect.