0

I have a custom script in a Purchase Requests spreadsheet. It generates new tabs and adds links from the new tab to 'register' (the first tab) in order to summarize the data.

Unfortunately, the tab name often gets generated in float format (206.0) instead of integer format, so the tab name references, which are always in integer format, don't work. I can't figure out why the float is appearing. It doesn't happen on every computer we try it on, only most, which makes it even more mysterious.

I've tried different methods for coercing it to an integer but haven't found a solution. All suggestions gratefully welcomed.

function newPR(e) {
  // First, set up the variables we'll need
  var ui = SpreadsheetApp.getUi()
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();  
  var sheet = sheets[0];
  var templateSheet = sheets[1];
  var highestPurchaseRequest = sheets[3];
  var today = new Date();
  var requestCount = sheet.getRange(6,1).getValue();  //getRange: row, column
  var lastRequestTab = parseInt(highestPurchaseRequest.getName(),10);
  var checkNeeded = ( requestCount != lastRequestTab );
  var loopCount = 0;
  /* Before starting, check whether the highest request number matches the 
     name of the highest estimate tab created so far. If not, try to fix it a few 
     times.  If you can't fix it automatically, flag an error and ask the user to 
     sort it out.
  */
  if( checkNeeded ) {
    while( loopCount < 100 && checkNeeded ) {
      if( requestCount <= lastRequestTab ) {
        requestCount++;
      } else {
        checkNeeded = false;
      }
      loopCount++
    }
    if( checkNeeded ){
      ui.alert("Something went wrong!",'The highest Purchase Request listed on the Summary tab is lower than leftmost Purchase Request tab in the spreadsheet.\n\nI\'ve tried to fix this automatically but haven\'t been able to. Maybe someone dragged tabs into a different order accidentally? The order should be: Register, TemplatePO, TemplateCS, Highest-numbered-request.\n\nPlease correct it and retry. ',ui.ButtonSet.OK);
      return;
    }
  }
    /* Everything looks good, so create a new sheet using "templatePO" as the template, 
       increment the request number and use it to name the new sheet; fill in the 
       current date & estimate number at the top 
    */
    requestCount = ++requestCount >> 0;
    ss.insertSheet(requestCount.toString(), 3, {template: templateSheet});
    // Now that it's made, we need another variable to store a reference to the new sheet
    var newSheet = SpreadsheetApp.getActiveSheet();
    newSheet.getRange(5,9).setValue( today );
    
    // Switch to the summary sheet and add links to the new data
    ss.setActiveSheet( sheets[0] );
    ss.insertRowBefore(6);
    var newCells = sheet.getRange(6,1,1,9);
    // Data from: PR# in I2, Date in I5, From in D4, For/Description in D5, Total in D7, sent in K2, received in K11, Notes in K5
    var requestData = [ [ requestCount, "='" + requestCount + "'!I5", "='" + requestCount + "'!D4", "='" + requestCount + "'!D5", "='" + requestCount + "'!K2", "='" + requestCount + "'!K11", "='" + requestCount + "'!D7","",""] ]
    newCells.setFormulas( requestData );
  
    // Switch back to the new sheet & get ready for user input
    ss.setActiveSheet( newSheet );
    newSheet.setActiveSelection("D5");
}
4
  • Can you provide a sample test Spreadsheet with the corerct structure for this to run? Commented May 18, 2022 at 8:19
  • Sure, try this: docs.google.com/spreadsheets/d/… Commented May 18, 2022 at 12:57
  • Seems to be working without any issue for me? Commented May 18, 2022 at 13:07
  • Please show what you have tried and add a brief description of your search efforts as is suggested in How to Ask (have you tried to use parseInt()? if so, how?) Commented May 18, 2022 at 13:45

1 Answer 1

1

To convert a number to a string with no decimal part, you can use toFixed(0).

Try changing

ss.insertSheet(requestCount.toString(), 3, {template: templateSheet});

to

ss.insertSheet(requestCount.toFixed(0), 3, {template: templateSheet});
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.