CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user6815917
user6815917

Reputation:

Google Apps Script: Using Var in a Formula?

I'm trying to create a Google script for a Sheet so that end users can import a list of email addresses from a sheet on another spreadsheet. Each user has their own sheet on this spreadsheet, and it is named after their own address. So, user1 has a sheet [email protected] on this spreadsheet, and user two has their own sheet, and so on.

I can use Session.getActiveUser().getEmail() to give me their email address, but I can't find a way to use that as a variable within IMPORTRANGE.

Is it possible to do this? If not, is it possible to achieve the same result through another method, like pointing at the sheet using the email var and storing the addresses in an array, which is then written to the target sheet?

Here's the relevant snippet of the script for reference. Note that I replaced the URL for the google sheet with sheetlink, and the bit that says ""useremail!A1:A100"" is where I was hoping to use the useremail var to fill it with the result of getEmail().

function importlist() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = 'Blacklist';
var useremail = Session.getActiveUser().getEmail();

if (ss.getSheetByName(sheetname) == null) {
    ss.insertSheet(sheetname);
    ss.getSheetByName(sheetname).getRange("A1").setFormula('=IMPORTRANGE("sheetlink","useremail!A1:A100")');
} else {
    ss.getSheetByName(sheetname).getRange("A1").setFormula('=IMPORTRANGE("sheetlink","useremail!A1:A100")');
    ss.getSheetByName(sheetname).activate()
};

Upvotes: 0

Views: 2778

Answers (1)

Sujay Phadke
Sujay Phadke

Reputation: 2196

You can't directly use the name of the variable in the string. It'll be treated as that word literally. You have a combine strings with a variable like this:

IMPORTRANGE("sheetlink", useremail + "!A1:A100")

Upvotes: 0

Related Questions