CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptdata-science
jeisenman19
jeisenman19

Reputation: 45

How to rename a new google sheet using cell references?

So currently, I am trying to make a google script that exports separate google sheets for each unique username -- i.e., to try to make a customizable report for each client. Basically, I have a list of unique usernames -- a list called uniqueUserName -- and I want to set the name of the new sheet to the "Name" which corresponds to the username. For example, suppose Sally1 is in the following table. The code would search through the usernames (with a for loop) and, once the for loop hits Sally1, the code would return Sally Wall -- i.e., the name corresponding to her username. Sally Wall would then be the new name of the document.

Username Name
Timmy Tim Jones
Sally1 Sally Wall
catsforlife John Mueller
ready2learn Cindy Rodney

I have tried the following code:

newSheet.setName(function(uniqueName, values){
   for (var i=0; i < values.length; i++) {
      if (values[i][0].Username === uniqueName) {
         return values[i][1].Name;
         break;
      }
   }
});

(I include the break function, because, if someone's name shows up twice, I don't want to copy their name twice. )

How would I have to adjust this code to serve these ends? Is this on the right track?

Honestly, I've been at this code for a while, looking up as much as I can, on stack overflow, YouTube, so your help is much appreciated!

Upvotes: 0

Views: 94

Answers (1)

mshcruz
mshcruz

Reputation: 1987

One problem is that you're passing a function to the setName() method, but it expects a string. One straightforward way to solve this would be to immediately call the function after it's declared, passing the arguments as well.

Another thing is that you're trying to access the properties Name and Username of elements in values, but, assuming you received values from getValues(), those properties don't exist. You can get the values you want by just using the indices, as you're already doing: values[i][0] and values[i][1].

Also, you don't need to use break because the loop will be interrupted by the return statement anyway.

Considering the above and assuming there's a sheet called Names (that has the names you posted) and another called Report Template, you can change your code to something like:

function newNamedSheetTest() {
  const uniqueName = 'Sally1';
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const values = spreadsheet.getSheetByName('Names').getDataRange().getValues();
  const newSheet = spreadsheet.getSheetByName('Report Template').copyTo(spreadsheet);

  newSheet.setName(function (uniqueName, values) {
    for (let i = 0; i < values.length; i++) {
      if (values[i][0] === uniqueName) {
        return values[i][1];
      }
    }
  }(uniqueName, values));
}

Another option would be to use filter and map to get the name of the sheet:

function newNamedSheetTestShort() {
  const uniqueName = 'Sally1';
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const values = spreadsheet.getSheetByName('Names').getDataRange().getValues();
  const newSheetName = values.filter(row => row[0] === uniqueName).map(row => row[1]);
  const newSheet = spreadsheet.getSheetByName('Report Template').copyTo(spreadsheet);
  newSheet.setName(newSheetName);
}

Upvotes: 2

Related Questions