Google-sheets – Button in column C in each row

google sheetsgoogle-apps-script

I have a spreadsheet with three columns and lots of rows.

Column A has a numerical ID
Column B is empty
Column C is empty

I want to have a button in each row in column C. When pressed, it should take the information in column A of the same row and do some processing on it and output the result in column B.
The processing will make use of outside servers to do some data fetching, hence I cannot do this on sheet load because of "data overload". (actually I tried first, but it takes ages, times out etc with many rows).
So I want to have it load that information adhoc, on the press of a button on the same line.

I have looked around and it seems that the only way to make buttons is to make a drawing of a button and then tie that button to a script. The problem with this approach is that the buttons aren't tied to a a cell so I can't get the current row number where the button is, to do processing on only that row.

Any fix?

Best Answer

Best is to make a custom menu, via Google Apps Script. This will allow you activate things by row.

Code

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [{name: 'Magic', functionName: 'myMagic'}];
  ss.addMenu('Do Stuff', items);
}

function myMagic() {
  var ss = SpreadsheetApp.getActive(), aRow = ss.getActiveCell().getRow();
  var nDate = Utilities.formatDate(new Date(), "GMT-1", "dd-MM-yyyy");
  ss.getRange("B"+aRow).setValue(nDate);
}

Your magic needs to happen in the myMagic function.

Example

I've prepared an example file for you: button vs. menu
You can run the code and see it in the file itself.

Related Solutions

Google-sheets – Google Scripts UI show new row as confirmation after form submit

I found that this works. Change the form submit button of the html file to: <input onclick="formSubmit();go();" type="button" name="mysubmit" id="mysubmit" value="Add Row"/>

In the html file, add this:

<div id="div">waiting...</div>
<script>
function callback(whatToWrite) {
document.getElementById("div").innerHTML=whatToWrite;
}
function go() {
  google.script.run.withSuccessHandler(callback).writeSomething();
}
</script>

In code.gs, add this:

function writeSomething() {
    var ss = SpreadsheetApp.openById("SHEETIDHERE").getSheetByName("DATA")
  var lastRow = ss.getLastRow();
  var range = ss.getRange(lastRow, 1, 1, 2);
  var Names = range.getValues();
  return Names;
}

These changes have the desired result of updating a div with the newest last row each time a new row is inserted. This makes a nice way of showing users that their form submission is successful, and the form remains open to receive more data.

I worked this out with the answer found here: how to use google.script.run as if it was a function. I will update my example Sheet, in case this will be helpful to someone else.

UPDATE: If you are editing existing rows, then you need to move the call of the go() function to inside the formsubmit function. so that the newly updated row is shown in the confirmation div, and not just the last row.:

function formSubmit() {
            google.script.run.getSireDamfromAutocomplete(document.forms[0]);
            go();
            }

Google-sheets – Google Spreadsheet partial row transpose and shift, partial row split

Older post, but I wrote a single-cell array formula that accomplishes this task and placed it into your editable sheet, in a new sheet I created for the purpose (Sheet2).

Headers are manually entered in Sheet2!A1:E1.

The following array formula is entered into Sheet2!A2:

=ArrayFormula(IF(ROW(Sheet1!A2:A)>COUNTA(Sheet1!A2:A)*18+1,"",{VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,{2,3},FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,MOD(ROW(Sheet1!A2:A)-2,18)+4,FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,MOD(ROW(Sheet1!A2:A)-2,18)+22,FALSE)}))