Long story short, I have in Google Sheets a spreadsheet for my personal budget/cashflow tracking, which has one accounting month per column and one category of income and expense per row. After I buy something, I have to add the amount of the purchase to the cell for the category of the expense in the column for the current accounting month, which is the same as my credit card billing period instead of the calendar month because it is much easier that way because I buy most things using my credit card account. I usually get the amount of the purchase from the recent transactions in my credit card account on the Web site of my credit card company, then use the Run dialog on Windows as a temporary buffer to manually/mentally add the amount of the purchase to the current value of the cell or use SpeedCrunch or IDLE for Python 2.7 (Python 2.7 for ever, even if it is now finally officially EOL) to have the computer add the amount of the purchase to the current value of the cell, then paste the new value into the cell. Since I usually do this manual process at least once every day, I thought I should finally get around to asking if there is a way, using the Web version of Google Sheets in Vivaldi on Windows 10 Enterprise for x86-64, that I can somehow partially automate this process so I can just copy and paste the amount of the purchase from my credit card account into the cell in Google Sheets and have the software automatically add the pasted value to the value of the cell in Google Sheets? I occasionally use Google Sheets for Android on my handheld computer to edit this same spreadsheet while I am away from my home and office but I will not worry about improving the efficiency of this process on my handheld computer for now because I am usually only doing this process on my home computer or office computer. Yes, I should ideally be using actual accounting software instead of a spreadsheet but Google Sheets works better for my use case because then I always have the current version of my document on all of my computers (stationary computer at home, stationary computer at office, handheld computer) without having to remember to do anything special, such as exit GnuCash on my home computer before going to my office or vice versa. In theory, I can remotely control my home computer from my office computer and vice versa to run a local/native accounting application but, in practice, I cannot reliably remotely control the other computer in either direction. I do not want to use GnuCash for Android, which is a different application than the original, desktop GnuCash and is not even written in the same programming language, because then I can only use the application on one of my computers. Heh, this was supposed to be a short version of my long story but I did try to avoid getting into unnecessary details/specifics.
This seems doable. I would suggest googling “google sheets automation”.
I searched the Internet with both DuckDuckGo, which I primarily use, and Google Search, which I occasionally still use, found some interesting articles and discovered the alternating colours and heat map features of Google Sheets but did not find any mention of what I wanted to do and think I have probably spent more time discussing and researching this issue than I would save even if I am able to do it after all.
directly paste + add to current value in a sheet doesn’t work AFAIK. However, you could create a second sheet and do a column sum of the second sheet in your first sheet, so you append a row in the second sheet each time you go to paste a new value.
Sheet1:
-cell A1=Sheet2!sum(A1:A1000)
Sheet2:
- column A, append values
Long story short, I finally created a topic in a Google forum, received only one reply, then cobbled together the following script based on preexisting source code (examples) written by other persons:
function onOpen()
{
var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu("Change cell value");
menu.addItem("Add 1 to current cell", "plus1");
menu.addItem("Subtract 1 from current cell", "minus1");
menu.addItem("Add specified value to current cell", "addValueFromUser");
menu.addToUi();
}
function plus1()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var numRange = sheet.getActiveRange()
var numAdd = numRange.getValue();
numRange.setValue(numAdd + 1);
SpreadsheetApp.flush();
}
function minus1()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var numRange = sheet.getActiveRange()
var numAdd = numRange.getValue();
numRange.setValue(numAdd - 1);
SpreadsheetApp.flush();
}
function addValueFromUser()
{
var ui = SpreadsheetApp.getUi();
var result = ui.prompt("Please enter decimal (currency) value to add to current/preexisting value of selected cell:");
//Get the button that the user pressed.
var button = result.getSelectedButton();
if (button === ui.Button.OK)
{
// Logger.log("The user clicked the [OK] button.");
// Logger.log(result.getResponseText());
var valueToAddFromUserAsString = result.getResponseText();
var valueToAddFromUserAsNumber = +valueToAddFromUserAsString;
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var numRange = sheet.getActiveRange()
var preexistingValue = numRange.getValue();
numRange.setValue(preexistingValue + valueToAddFromUserAsNumber);
SpreadsheetApp.flush();
}
/*
else if (button === ui.Button.CLOSE)
{
Logger.log("The user clicked the [X] button and closed the prompt dialog.");
}
*/
}
Unfortunately, this solution seems to only work in the Web version of Google Sheets, not in Google Sheets for Android, which seems to lack the menus of the Web version. I am not worried about this for now, though, because I usually use the Web version on my desktop computers instead of using the Android version on my handheld computer. I do not use a tablet computer because it is redundant for my use case so I do not know if Google Sheets for Android is different on a tablet computer than on a smartphone in this case. I do still have a Kobo Vox model of tablet computer from 2011 but it has Android 2.3 (Gingerbread, rooted with Gingerbreak) so I highly doubt that it can still run a current version of Google Sheets for Android. I do not use Apple iOS so cannot test with Google Sheets for Apple iOS. I do not have an ARM Mac either so cannot run an Apple iOS application that way. Does anyone know how to get the menus that the Web version of Google Sheets has in Google Sheets for Android on a smartphone? If it matters, I currently use the official Android 8 on an LG G5, specifically LG-H831, from Fido in Canada but I switched to Lucky Mobile, which is basically Bell Mobility, in 2019. I tried to open my spreadsheet in the Web version of Google Sheets in Vivaldi on my handheld computer but the computer automatically switched to using the Android applications for Google Drive and Google Sheets instead of letting me open the spreadsheet in a Web browser. Sigh.
Update 2020‑12‑31: I found a way to open my spreadsheet in Vivaldi for Android on my handheld computer and was able to add a purchase using my new command but it was much easier said than done because the software is flaky and kept only showing columns A to AD instead of to AH but I need to edit the rightmost column, which is currently AH, because the rightmost column is always the column for the current accounting month.
Update 2021‑10‑02: See this topic I started: how to disable App Links feature? | Vivaldi Forum
I still cannot subscribe to my financial accounts as an RSS/Atom news feed but my credit card company, which is currently Collabria based in Calgary, finally added a feature to automatically email their customer when a transaction, at least a purchase, occurs in the account of the customer, so better to have this type of notification decades late than never.
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.