Log File CopyTo Function Gives REF! Error With VLOOKUP Functions

[Jas]

I'm attempting to capture data and have it be stored as log files. The cells in the original data contain VLOOKUP formulas with absolute references to a sheet called "Index". I'm able to copy everything over, but the destination cells with VLOOKUP show up as a #REF! error saying "Unresolved sheet name 'Index'". I believe this error has something to do with renaming sheets from "Copy of...[name]" to "[name]". How do I work around this?

This error can be resolved by entering edit mode in the cells and pressing enter, but it's not as automated as I'd like it to be.

var ss = SpreadsheetApp.openById(''); 

var sheet = ss.getSheetByName('Log'); 

var index = ss.getSheetByName("Index"); 

sheet.copyTo(log_file); 

index.copyTo(log_file); log_file.deleteSheet(log_file.getSheetByName('Sheet1')); log_file.getSheetByName("Copy of Log").setName("Log"); 

log_file.getSheetByName("Copy of Index").setName("Index");

[Ed Sambuco]

Hmmm ...  maybe you should alter the sequence of execution.  You're copying the log sheet with the VLOOKUPs first, and then the index sheet.  There may be complications with the exact processing order, but if the copy of the log completes before copy of the index, then I can see an issue.

You might even want to copy the index in a manner to ensure that it completes its copy entirely before the log sheet is copied..   Separate functions?  Separate scripts?.

--
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/e785c27f-5983-4586-88e5-2bc0a2f1e86an%40googlegroups.com.

[Ed Sambuco]

I had some time toi check this out... you do need to reverse the copy sequence so that the index sheet copies before the log sheet (assume no vlookup in index and vlookup to index from log.)  You also need immediate sheet rename.  See sample script.:

function myFunction() {

  var ss1 = SpreadsheetApp.open(DriveApp.getFilesByName('Spreadsheet_0413_1').next());

  var sheet = ss1.getSheetByName('Log'); 

  var index = ss1.getSheetByName("Index"); 

  var ss2 = SpreadsheetApp.open(DriveApp.getFilesByName('Spreadsheet_0413_2').next());

  index.copyTo(ss2).setName('Index');

  sheet.copyTo(ss2).setName('Log');

  ss2.deleteSheet(ss2.getSheetByName('Sheet1'));

}

[ant ant]

Big Thanks!! 

вт, 11 апр. 2023 г. в 03:46, Jas <reshiram...@gmail.com>:

--