“Write to sheet” instead of “Store to arrays, than write to sheet”

[CM]

I have a script that pulls messages from Gmail (filetered by label), than writes to a Google Spreadsheet (only if the data isn't present)

The way it works now is:

1. 1. GETS INFO FROM GMAIL AND STORES IN ARRAYS
2. 2. APPENDS ARRAY INFO TO THE SPREADSHEET

What I would like is that it would skip the "store to arrays" phase (something like):

1. 1. GETS INFO FROM GMAIL AND APPENDS INFO TO THE SPREADSHEET

Here is the code I have working now:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////



//GETS INFO FROM GMAIL AND STORES IN ARRAYS, IF IT'S NOT ALREADY PRESENT IN THE SPREADSHEET

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

function getInfo_(q) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

  var messageIdRange = sheet.getRange(2,7,sheet.getLastRow());

  var label = GmailApp.getUserLabelByName(q);

  var threads = label.getThreads();

  for (var i = 0; i < threads.length; i++)  {

    var msgs = threads[i].getMessages();

    for (var j in msgs) {          

      var message = msgs[j];

      var finder = messageIdRange.createTextFinder(message.getId());

      if (finder.findNext() != null) continue;







      this.froms.push([message.getFrom().replace(/^.+</, '').replace(">", '') ]);

      this.tos.push([message.getTo().replace(/^.+</, '').replace(">", '') ]);  //Cristian added

      this.subjects.push([message.getSubject().replace(/<.*?>/g, '\n').replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')]);

      this.bodys.push([message.getPlainBody().replace(/<.*?>/g, '').replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')]);

      this.names.push([message.getFrom().replace(/<.*?>/g, '').replace(/^\s*\n/gm, '').replace(/^\s*/gm, '').replace(/\s*\n/gm, '\n')]);

      this.dates.push([message.getDate()]);

      this.emailPath.push(['https://mail.google.com/mail/u/0/#inbox/' + message.getId()]);

    }

  }

}




//////////////////////////////////////////////////////////////////////////////////////////////////////////////

//APPENDS ARRAYS INFO TO THE SPREADSHEET

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

function appendData_(Sheet1, array2dFroms, array2dtos, array2dSubjects, array2dBodys, array2dNames, array2dDates, array2dURL) {

  Sheet1.getRange(Sheet1.getLastRow() + 1, 1, array2dFroms.length, array2dFroms[0].length).setValues(array2dFroms);

  Sheet1.getRange(Sheet1.getLastRow() + 1 - array2dFroms.length, 2, array2dtos.length, array2dtos[0].length).setValues(array2dtos);   //Cristian added

  Sheet1.getRange(Sheet1.getLastRow() + 1 - array2dFroms.length, 3, array2dSubjects.length, array2dSubjects[0].length).setValues(array2dSubjects);

  Sheet1.getRange(Sheet1.getLastRow() + 1 - array2dFroms.length, 4, array2dBodys.length, array2dBodys[0].length).setValues(array2dBodys);

  Sheet1.getRange(Sheet1.getLastRow() + 1 - array2dFroms.length, 5, array2dNames.length, array2dNames[0].length).setValues(array2dNames);

  Sheet1.getRange(Sheet1.getLastRow() + 1 - array2dFroms.length, 6, array2dDates.length, array2dDates[0].length).setValues(array2dDates);

  Sheet1.getRange(Sheet1.getLastRow() + 1 - array2dFroms.length, 7, array2dURL.length, array2dURL[0].length).setValues(array2dURL);

  }

[Michael Ellis]

Are you trying to combine the two functions into one? The logic in the code seems generally good. Not sure what improvement you are seeking.
It could be even more efficient by changing the logic to do only a single setvalues. That does not seem to be what you asked about.

[CM]

Yes, I was thinking to further optimize the script, with the main purpose of improving speed. It runs decently fast, as long as there aren't a lot of emails. I'm new to coding, so I don't really know if it can ve done as I imagined.

[CM]

"It could be even more efficient by changing the logic to do only a single setvalues.  That does not seem to be what you asked about."

 How would you approach that?

[Brian Pugh]

I would love to try your script, but I keep getting 'no functions to run' when I try to run it.

[Cristian Maria]

Hi Brian,

I haven't posted the complete script, which is of course more complex. 

I'm on mobile now, so I won't be able to do it until later tonight. 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/909cdf8b-0ffd-4ad6-84c7-21b59f5f993d%40googlegroups.com.

[Brian Pugh]

Oops! :0)

Thank you, Cristian. I look forward to trying it. It could be very useful in our school setting. 

Brian

Brian Pugh IT/Educational Technology Support Staff Associated Hebrew Schools Danilack Middle School p: 416.494.7666, 153 e: bp...@ahschools.com www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAG4TV7QCdnaP6y0GkyA7c2Ckuj6zheTrHM-fzRCDgu3%2BLwR9zQ%40mail.gmail.com.

[Brian Pugh]

Cristian, I apologize for asking again, but might you still be able to share your entire script with us?

Hoping...

Thank you very much,

Brian

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.