How to rename Google spreadsheets (files) and populate their contents with values from a sheet

Question

Desired to have the following Google Spreadsheet function, which (1) renames a spreadsheet (target file) and (2) populates one of its sheets based on the values contained in another spreadsheet (source file), loop through from the second row onwards until all spreadsheets listed have been renamed and populated.

All the values required for renaming the target files are in cell range D2:D.

All the values required for populating the target files are in cell range A2:D

All the IDs of the target files are in cell range G2:G.

There are no blank rows between and including the second row and the last data-containing row, and all rows with data in this range are to be used. Each of the columns (A, B, C, D, G) has the same number of data-containing rows.

The value of the number of rows which contain entries to be used is in cell range E1, if that's of any use (not used in this function).

function rnmCpyRngTo() {
  //rename target spreadsheet and populate range
  //in target sheet from values in source spreadsheet
     //get source sheet in source spreadsheet
     var ssss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CopyToWrdLists");
     //get source cell range containing value of name to be given to target spreadsheet            
     var scrvn = ssss.getRange("D2").getValue();
     //get source cell range containing values to copy to target sheet cell range
     var scrvr = ssss.getRange("A2:D2").getValues();
     //get source cell range containing values of target spreadsheet ID
     var scrvid = ssss.getRange("G2").getValue();  
     //get target spreadsheet ID
     var tssid = DriveApp.getFileById(scrvid); 
     //get target sheet
     var tss = SpreadsheetApp.openById(scrvid).getSheetByName("Wordlist"); 
     //rename target spreadsheet
     tssid.setName(scrvn); 
     //copy values to target sheet cell range
     tss.getRange(1, 1, scrvr.length, scrvr[0].length).setValues(scrvr);
}

Question 15616530 seemed close to the desired outcome but was unable to modify it to suit. Help would be much appreciated.

Added:

     //get source cell range containing values of target spreadsheet ID
     var scrvid = ssss.getRange("G2").getValues();

Source sheet has been shared here so that anyone with link can edit.

Assuming your signed into Google, this folder has been shared.

The folder "Target_Spreadsheet" should have nothing in it.
Run script "Create wordlists - TOMS" from user menus "Wordlists" to create the documents, and
Run function "Rename and populate - TOMS" from the user menus to rename and populate the first listed file only.

Repeat as necessary. That's what I'm up to.

Thanks.

Sharing your spreadsheet or a representative data set of columns used for this script would be helpful — Jack Brown, Mar 19 '17 at 07:14
Link to spreadsheet now provided and code has a line that was left out. — James Starey, Mar 19 '17 at 10:56
var scrvid = ssss.getRange("G2").getValues(); returns a two-dimensional array of values even though it contains only a single value. I would change it by removing the 's'. var scrvid = ssss.getRange("G2").getValue(); which will return the single value in G2 — ScampMichael, Mar 19 '17 at 12:28

score 1 · Accepted Answer · answered Mar 19 '17 at 16:29

This should do the trick, just put your code in a for loop:

function rnmCpyRngTo() {
  //rename target spreadsheet and populate range
  //in target sheet from values in source spreadsheet
     //get source sheet in source spreadsheet
     var ssss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CopyToWrdLists");
     //get source cell range containing value of name to be given to target spreadsheet 
     var data = ssss.getDataRange().getValues()        // All the data in sheet, since there not empty rows. 
     Logger.log(data)
     var dataLength = data[0][4]                       // Get number of rows from cell E1, can use data.length instead
     for (var i = 1; i <= dataLength ; i++){           // If you rather use data.length change the condition to i < data.length
       var scrvn = data[i][3]                    // Get Rename value
       var scrvr = [] 
       scrvr[0] = data[i].slice(0, 4)            // Get Copy Values
       var scrvid = data[i][6]                   // Get of SpdSheet
       var tss = SpreadsheetApp.openById(scrvid) // Get Spreadsheet
       tss.rename(scrvn)                         // Rename Spreadsheet
       Logger.log(scrvr)
       tss.getSheetByName("Wordlist").getRange(1, 1, scrvr.length,scrvr[0].length).setValues(scrvr)  // Set Copy Values to the sheet

     }

}

Hope it helps!

Wow. Thank you very much. Works like a charm. My up votes won't be displayed due to number of rep points. Hopefully, someone else will find this as useful as I do. — James Starey, Mar 19 '17 at 23:22

score 0 · Answer 2 · edited Jan 30 '23 at 12:59

0

just use

newSheet.updateProperties({title: "new title"})

for example

edited Jan 30 '23 at 12:59

Webdeveloper_Jelle

2,868
4
29
55

answered Jan 28 '23 at 18:24

Luiz Cesar

1

How to rename Google spreadsheets (files) and populate their contents with values from a sheet

2 Answers2