Is there a way to achieve “left join” google sheets using Google Apps Scripts? - google-apps-script

First, I attached the google sheet here PICC sheets to make more sense
I am kind of new to Apps Script, recently I have a requirement to use apps script to implement same logic this query
You can see in the google sheets, I need left join sheet "PICC Data" and sheet "FTP Mapping" on PICC data.gl = FTP_mapping.BS_GL,
also, when FTP_mapping.FTP_GL, PICC_data.amt need to multiple "-1"
I want to know how to use Apps Script to implement the SQL query above in picture?
the ideal output is "Sheet5" in the sheets I attached
I wanna know if it is possible to achieve the output using apps script
If yes, can you please show me how the apps script code works?
Thanks very much

Related

Using BigQuery API with Google App Maker

Has anybody had any experience with using BigQuery with Google App Maker? Specifically, creating a data source for a Google Drive Table through Apps Script.
I use a script in google sheets to use BigQuery API, run a job and return the output to a sheet, but i'm unsure how to modify this to work in App Maker - any advice?
best wishes
Dave
Sorry for the vague answer, maybe someone else can provide a better one with a concrete example.
If you've already got an Apps Script script, then this should be easy. What you probably want to do is implement a Calculated Model in order to get the results from BigQuery to your application. In short, a Calculated model will let you define a custom schema, and then write Apps Script code to query BigQuery and then create, fill, and return records (which will be very similar to the code you wrote to fill sheets). Once you've done this you can use the calculated model like you would a normal SQL or Drive Tables model.
You can read more here: https://developers.google.com/appmaker/models/calculated
and using this sample (although the use case for calculated models in this sample is quite different than what you're doing): https://developers.google.com/appmaker/samples/calculated-model/
Just like Apps Script you'll also need to enable the Big Query Advanced Service, you can find that in the application settings screen, all the way at the bottom, ("Advanced Services")

To copy the cell text and the comments to another sheet using google apps script?

I have a google spreadsheet with comments in each cell. Can i programatically read, copy and the cell text and the comments to another sheet using google apps script? I need this facility with comments and not with notes.
There is currently no way to interact with commends for Google Apps script.
You can follow this bug to see how the implementation of that is coming along (not very fast).
Just discovered, that .moveTo(target) is able to move comments even between sheets.
You can read about it here: https://developers.google.com/apps-script/reference/spreadsheet/range#movetotarget
Hope this helps somebody.

Importrange to run automatically?

Using Google scripts, I'm creating a bunch of spreadsheets each of which contains an importrange function. In new google sheets one needs to allow access for that importrange to fetch data from a source file - I was wondering whether it was possible to make it automatic using Google scripts and get round the need to press "allow" manually each time?
It is definitely possible to write an importrange function in Apps Script, but you'll need to run it too, so I don't know if it's going to be any better than clicking "allow".
I guess that, depending on your use-case, you could write a single script in the source sheet that pushes the data to the other sheets and possibly even create them automatically for you. Therefore saving you this click per sheet and possibly some other clicks. We can surely help you here in StackOverflow, but it's mostly working out problems, you'll have to put in some effort to develop it yourself. Take a look at Apps Script page to get started.
I was facing the same issue and I don't think access can be granted through script. It needs to be done manually in each spreadsheet.
Best part is it needs to be done only once in any spreadsheet then it will update automatically.

Use a Google Docs Spreadsheet as a datasource for a dynamic Google Sites webpage

I have a Google Form that feeds a Google Docs Spreadsheet. I'd like to--in turn--have that Google Docs Spreadsheet feed a webpage.
In plainer English, babysitters fill out the form to sign up to be in our community's Babysitter Directory. The spreadsheet houses all of the data. I'd like to code a webpage to pull selected bits of the data for the online directory.
I've tried doing a separate sheet in the spreadsheet, using a QUERY to select the columns that I want to include (and the order in which I want to include them), publishing that sheet to the web, then embedding that sheet into the webpage in an iFrame. And that works.
But even with the QUERY, there are SO many columns that users need to scroll WAY over to the right to see all the data for each babysitter. It's unwieldy.
What would be way better would be if I could break the data for each entry over multiple lines and do some nice formatting for a directory, rather than just a linear spreadsheet. So that, essentially, each babysitter's "entry" in the directory is more than 1 line long. Does that make sense?
If I was working in Office, I would know exactly what to do: use the Excel spreadsheet as the datasource for a Word Mail Merge and I would put move the fields around on the page to make it all look good.
And, to be sure, if I can do this in a Google Doc, then embed the Doc into the webpage, that's fine, too. But I would think there's some way I can do it directly in the Google Site?
Can I?
If anybody has even just a reference page for me to take a look at, I'd appreciate it.
Thanks!
Are you trying to do this in Google Sites? If so, you can embed the entire spreadsheet on the page, but if you only want certain columns, you can try inserting an Apps Script widget on the page.
You need to know how to write a Google Apps Script that will run JS functions and render HTML, here is a tutorial
To create the Script that can run on your page, go to:
More > Manage site > Apps Script > Add new script
Here's also a link to how to interact with Spreadsheet data.
What you want to do is more or less a database interface that uses a spreadsheet as 'data holder', depending on your programming skills it can be quite easy or very hard...
Here is an example of such a webapp, its has probably too many fields and features but the general idea is the same (a quick search tool and a window to show results).
It that what you had in mind ?
If so I can share the code to help you to get started but if you are not familiar with javascript it will probably need too much effort to get through.

How should I reuse functions on a number of different Google Spreadsheets?

I have now written a number of different functions in Google Apps Script that I am using on a growing number of Google Spreadsheets.
Every time I make a small edit to one of these functions I then have to go into each spreadsheet and make the same correction.
Is there a way to "import" the functions to each spreadsheet so that I only have to edit the one "master" function and it is then updated on every spreadsheet?
Google has just release a new feature called Libraries, it is an elegant way to do what you're asking for. Here is the documentation, read it attentively because it's a multi-step process.
See this page for how to create and share functions in your sheets using Google Apps Scripts. The scripts are written in javascript and there are some limitations on how they can be used.
https://developers.google.com/apps-script/guide_writing_scripts#CustomFunctions

Resources