2

Working with large sets of data in Google Sheets with Apps Script is pretty difficult. I was hoping to be able to use AlaSQL to help--there's a really helpful port of it just for Apps Script. However, I'm getting some extremely unexepected results. Because of the confidentiality of the data I'm working with, I'm going to generalize as much as possible while still providing the important details.

The document I'm working with has to be refreshed quarterly--i.e. new data needs to replace the old data. However, the new dataset may overlap the old dataset. In other words, some records from the old dataset will still be present in the new dataset. There are some manual-entry columns (like comments, dates, etc.) that we would like to be able to pull forward into the new dataset. Conceptually speaking, this is a super easy database operation. It would look something like this (in pseudocode):

SELECT 
  new.*,
  old.notes,
  old.dates,
  old.etcetera
FROM
  new_data_table AS new
  LEFT OUTER JOIN old_data_table AS old ON new.primary_key = old.primary_key

Here's where it gets a bit weird. I'm giving AlaSQL a query with two table parameters. The query looks like this:


SELECT MATRIX
  new.[26], //Primary key
  new.[1], //Info1
  new.[2], //Info2
  new.[3], //Info3
  ...
  old.[1], //Info6
  ...
FROM ? AS new
LEFT OUTER JOIN ? AS old ON new.[26] = old.[0]

Then, I'm feeding it to the query like so:

AlaSql(query, [new, old])

But my results aren't what I expect. As far as I can tell, the join itself is working great--the PKs on both tables are matching, and they're being brought together. However, the output I'm getting has columns that are in a completely different order from what I'm specifying in the SELECT statement. In fact, I have columns in the data (from the old table) that I'm not even including in the SELECT statement at all! I'm getting something in the neighborhood of:

Here are the things I have verified:

  1. Simple select statements on a single table work as anticipated. Columns are returned in the correct order.
  2. My SELECT clause is ordered correctly, assuming that the column named new.[0] is the 0th column in the new table. This is an assumption I could be making incorrectly.
  3. My key columns are the correct numbers--I have manually checked and I can verify by looking at my table variables during debug mode.

Any ideas?

Steven Elliott Johnson
  • 33
  • 3

1 Answers1

1

I have been using alaSQL with Google Apps Script for a while, and LEFT JOIN works well. I always specify each SELECT column explicitly in the syntax : table.column .

I use the SUPERSQL function to call alaSQL. I suggest you take a look at https://github.com/chicagocomputerclasses/SUPERSQL-Google-Sheets-Function and also watch the videos at https://www.youtube.com/playlist?list=PLv9Pf9aNgemvJdbQrfqXIRs8ssNujA8Vz

If you need more help, please post your exact Apps Script code but with fictitious data.

lamontfr
  • 56
  • 2