CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
Bruna B
Bruna B

Reputation: 173

Interact through an array generated from a Google Sheet | Google Apps Script

I'm new to Google Script and I'm learning at work. What I am trying to do is a script that, based on a google sheet, check if the subject of the email contains the String present in one of the rows and save the attachment on the google drive. The ID for this folder is also on the same google sheet.

So the structure would be:

Column A Column B Column C
x EXE2928 folderID29382938
x EXE823 folderID29383994
x EX3948 folderID55154988

The script to send save the attachments I am already using and it works. And if I read the information on the google sheet one by one, I can send it to the folder correctly. But this is not optimal, since there are a lot of rows.

What I tried so far was following function to get the rows as array and than interact over it

  var dataspreadsheet = SpreadsheetApp.openById("SpreadsheetID");
  var sheet = dataspreadsheet.getSheetByName("Sheet1");
  // Just looking at the Column B
  var data = sheet.getRange(1,4,5).getValues();
  var header = data[0];
  data.shift();
  
// ...

        for (var l = 0; l < data.length; l++) {
          if (filename.indexOf(data.length[l]) !== -1) {
          // Here I still need to get the folder ID from the same google sheet
            folderName = folder1;
          } else {
            folderName = folder2;
          }

Could you give me some support or ideas on who to go on with this script?

Thank you in advance!

Upvotes: 0

Views: 77

Answers (1)

Mart&#237;
Mart&#237;

Reputation: 2851

This answer assumes that you are using GmailApp to retrieve the emails. Also, it can only be considered a bit of a guideline, since I have not spend the time to test and measure code.

To keep in mind

Tooling

To optimize code you the first you need is tooling.

The most important tool is to measure that time that some part takes to run. You can do so with console.time and console.timeEnd (see console timers reference). This methods will allow you to measure the time it takes between them.

For parts of code where you don't require Apps Script classes or methods, you can test them locally to measure the performance with any other tool. Just be aware that it may not perfectly translate to Google Apps Script.

Know what you want to achieve

What is fast enough? This is something that you need to know before starting. This is usually specified for the reason you are optimizing in the first place.

Best practices

Read the official Apps Script best practices guide. It has a lot of advises that hold almost always true.

Know your system

A lot of times there are constraints on the system that you haven't even considered.

For example: if the string to search is always at the start of the subject, you may be able to make a more specific code that performs better.

Another example: Does this kind of threads only have a single email, or multiple of them? This can change the code a lot.

Measure everything

Don't assume. Measure. Sometime things that seem like should be slower are faster. This is more true the more you optimize. It's highly recommended to get a baseline of the current time and work from there.

Test the simple first

Don't get carried away trying complex code. Some time simple code is faster. Sometimes it's not faster but it's fast enough.

Weird can be good

Try to think outside the box. Sometime weird solutions are the fastest. This may reduce readability.

An example would be to generate a regular expression with all the values and use it to detect if it contains one and which. This could be slower or faster, but it's worth trying.

const r = /(EXE2928|EXE823|EX3948)/  // generate this dynamically
const m = r.match(string)
if (m != null) {
  const key = m[1]  // key is the value included on the subject
}

Some ideas

Get the minimum data and only once

You only need the mapping of columns B (text to find) and C (folder to send) to do what you need. Simply get that data. You will need to iterate so there's no need to transform the data. Also skip the headers.

var mapping = sheet.getRange(`B2:C`).getValues()

Also try to limit the number of Gmail threads that you read.

Organize the emails

I'd try moving the emails into a more easily digestible data to iterate.

Change when it's executed

I don't know when this code is executed but changing it could change execution time.

Changing platform

Google Apps Script may not be the best platform to be using. Calling the APIs directly from a client library (there is a python one) may be better.

References

Upvotes: 1

Related Questions