In order to edit multiple rows of google sheets values, I need to use the bulk update function, but this requires that I list the primary key as one of the elements that it will make changes to. For most usages, this would be fine as they should be equal, but my primary key is equal to '=row()', meaning that retool is overwriting this formula to the actual row value. This causes an issue when a row is deleted or inserted in the existing rows as the value isn't updating based on which row it is in.
1 Like
that's an interesting use-case. Using =row like this felt so dirty that I spent a good hour coming up with alternatives but none were less dirty so I just outsourced the dirty work to Google.
From your sheet, click Extensions -> App Script and add this function to your Code.gs which iterates through each row and reset the primary key to =row().
const resetPrimaryKey = () => {
// active = the spreadsheet from which you clicked through
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// sets the sheet
const allSheets = spreadsheet.getSheets();
const firstSheet = allSheets[0];
// const thisSheet = spreadsheet.getSheetByName("index");
// prepare range of non-empty rows
const numRows = firstSheet.getLastRow();
const range = firstSheet.getRange(`A2:A${numRows}`);
// redraws the column starting at 1
range.setFormulaR1C1("=row()-1");
};
Save and run it for the first time, which should prompt you to auth.
Navigate to the triggers (sidebar -> clock icon) and create a new one (bottom right).
That's it, test it out
.
Bear in mind that the script needs a few ms to run. Debounce any success event handlers for at least 1 sec.
While it usually takes less than a second you'll eventually see data without an ID. You can re - trigger the query if an empty id is found with a simple maxTries state to stop it from looping infinitely. Or you know, something nicer :))
Hope that helps 
2 Likes
Thank you for the work around! I was also thinking about using app script as a resolution, but we've noted it to have an appreciable failure rate that is increased via having multiple queries active and the amount of data being queried. Instead, we used A1 notation in retool for essentially the same purpose of writing over the row with '=row().'
2 Likes
Thank you both! We also have a feature request for this, so I'll post here when we've shipped a fix