Is there a way to create a script that go and find the same username in another workbook and then ta-CodePudding

My problem is that I would like to modify my existing script in appscript in order that no emailaddress is imported in the mailroom tracker. So what I would like the script to do is that when I run the email sending button:

the script go seach for the same recipient name in the other workbook (other google sheet) and then take the corresponding email address in the next cell on the right
When it has it store it temporarly in an array
use it and then delete the table
The document containing the email should not be open on the computer but virtualy

Here is an example of the sheet and script I would like to modify (the url are all accessible) :

mailroon tracker : "https://docs.google.com/spreadsheets/d/16ub8m4mHscHuOhuDxNHJI5XTrNPqd2qNdBCjeansUI8/edit?usp=sharing"
List of people and email address: "https://docs.google.com/spreadsheets/d/1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels/edit?usp=sharing"
List item

If you could help me with it and explain me in big lines what you did that would help me so much !!

Kind regards, Constantin

CodePudding user response：

You can use openById() or **openByURL ** method to extract data from that email sheet.

function extractEmails() {
  var ss = SpreadsheetApp.openById('1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels')//This will access your spreadsheet "virtually" 
  var ssname = wb.getSheetByName('Employee_Email_SheetName')//This will access the following sheet in that spreadsheet, make sure to use proper sheet name
  var emailData = ws.getDataRange().getValues() // this will get the data from that sheet
  return emailData
}

Suppose you just need emails from specific column from that sheet, ex: C1:C20 has your emails information, than in that case you can replace

ws.getDataRange().getValues()

with

ws.getRange('C1:C20').getValues() //C1:C20 is your email range

Reference:

openById(id)

openByUrl(url)

CodePudding user response：

You can fetch the informations by using

function myFunction() {
  var wb = SpreadsheetApp.openById('1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels')
  var ws = wb.getSheetByName('On site')
  var data = ws.getDataRange().getValues()
  Logger.log(data)
}

reference

openById