Problems switching sheet using google script

65 Views Asked by At

I'm submitting a form which is saving data. I want to have a script take the data and I want to file that data away in another sheet.
One column has the name of the other sheet I'm going to find. I iterate my way through the data find the correct row, and then I'm attempting to select that sheet.

    spreadSheetfile.getSheetByName(move_name);

move_name is a variable that I know has a string in it with the correct name.

The total code is here. Yes, it is ugly but until switching sheets it actually puts the right data in the right place. I normally (because I'm a below average coder) write something that works and then clean it up as I get better.

I could write this in VBA. But I'm attempting to switch to keeping this on Google Form/Sheet.

function TestMacro() {

//set variables
  var spreadSheetfile = SpreadsheetApp.getActiveSpreadsheet();
  var specificsheet = spreadSheetfile.getSheets()[0];
  var counter = 2;
  var specificrange = specificsheet.getRange(counter, 2, 1,1);
  var Processed_value = specificrange.getValues();
  var responCE;
  var row_hold;
  var move_name = "String";
  var move_status;
  var move_now;
  var move_detail;
  var move_project;
  var sheetCOUNTER=0;
//find first unprocessed row
  while (Processed_value==1){
    counter = counter+1
    specificrange = specificsheet.getRange(counter, 2,1,1);
    Processed_value = specificrange.getValues();
  };
//save unprocessed row
  row_hold = counter;
//as long as not yet existing row keep going
  specificrange = specificsheet.getRange(row_hold, 1,1,1);
  Processed_value = specificrange.getValues();
  while (Processed_value!==""){
    specificrange = specificsheet.getRange(row_hold, 3,1,1);
    move_name = specificrange.getValues();
    specificrange = specificsheet.getRange(row_hold, 1,1,1);
    move_now=specificrange.getValues();
    //set up loop
    counter =4;
    specificrange = specificsheet.getRange(row_hold, counter ,1,1);
    move_project = specificrange.getValues();
    //find what data needs to be moved
    while(move_project==""){
      counter = counter+1;
      specificrange = specificsheet.getRange(row_hold, counter ,1,1);
      move_project = specificrange.getValues();
      if(move_project==""){}
      else{break};
    };
    //get the rest of the data together
    specificrange = specificsheet.getRange(row_hold, counter+1 ,1,1);
    move_status= specificrange.getValues();;
    specificrange = specificsheet.getRange(row_hold, counter+2 ,1,1);
    move_detail= specificrange.getValues();;
    //go to new sheet
    responCE = Browser.msgBox("8 Find new sheet", move_name, Browser.Buttons.OK_CANCEL);
    spreadSheetfile.getSheetByName(move_name);
    //find the right row
    counter =2;
    specificrange = specificsheet.getRange(counter,1,1,1)
    Processed_value = specificrange.getValues();
    responCE = Browser.msgBox("9 Find right row", Processed_value, Browser.Buttons.OK_CANCEL); 
    while(Processed_value!==""){
      //place the data
      if(Processed_value==move_project){
         specificrange = specificsheet.getRange(counter,2,1,1);
         specificrange.setValue(move_now);
         specificrange = specificsheet.getRange(counter,3,1,1);
         specificrange.setValue(move_status);
         specificrange = specificsheet.getRange(counter,4,1,1);
         specificrange.setValue(move_detail);
         move_detail = "";
        };
        counter = counter+1;
        specificrange = specificsheet.getRange(counter,1,1,1);
        Processed_value = specificrange.getValues();
        responCE = Browser.msgBox("10 Find right row", Processed_value, Browser.Buttons.OK_CANCEL); 
     };
    //new project make sure to add it
    if(move_detail!==""){
         specificrange = specificsheet.getRange(counter,1,1,1);
         specificrange.setValue(move_project);
         specificrange = specificsheet.getRange(counter,2,1,1);
         specificrange.setValue(move_now);
         specificrange = specificsheet.getRange(counter,3,1,1);
         specificrange.setValue(move_status);
         specificrange = specificsheet.getRange(counter,4,1,1);
         specificrange.setValue(move_detail);      
    };
    responCE = Browser.msgBox("11 Go back", "Form Responses 1", Browser.Buttons.OK_CANCEL); 

    //go back to original sheet
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName("Form Responses 1"), true);
    specificsheet = spreadSheetfile.getSheets()[0];
    specificrange = specificsheet.getRange(row_hold, 2,1,1);    
    specificrange.setValue(1);
    row_hold = row_hold+1;
    specificrange = specificsheet.getRange(row_hold, 1,1,1);
    Processed_value = specificrange.getValues();
  };

  responCE = Browser.msgBox("12 Winnning!", "tiger blood", Browser.Buttons.OK_CANCEL);
  return;



    responCE = Browser.msgBox(Processed_value, counter, Browser.Buttons.OK_CANCEL);
    if(responCE=="cancel"){
    return;
    };
};
0

There are 0 best solutions below